Results 1 to 11 of 11

Thread: [RESOLVED] Dynamic IN Clause

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Resolved [RESOLVED] Dynamic IN Clause

    This isnt related to my other thread but I have a dynamically generated csv list of ID numbers that I want to use for an IN clause in my sp but of course Im getting cast'ing errors as the permission_id field is an Int type.

    If I cast it to any other type it will fail since it has commas in it but that is needed.


    Code:
    DECLARE @user_id int 
    DECLARE @perm_ids varchar(max)
    SET @perm_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40'
    SET @user_id = 1372
    
    SELECT   
    	U.user_id,   
    	U.first_name,   
    	U.last_name,   
    	UP.permission_id,   
    	P.permission_constant,   
    	P.permission_description  
    FROM   
    	[tblUserPermission] UP WITH (NOLOCK)  
    INNER JOIN   
    	tblUser U WITH (NOLOCK) ON UP.user_id = U.user_id  
    INNER JOIN   
    	tblPermission P WITH (NOLOCK) ON UP.permission_id = P.permission_id  
    WHERE   
    	U.User_Id = @user_id  
    		AND
    	UP.permission_id IN (@perm_ids)
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Dynamic IN Clause

    Yeah, unfortunately that doesn't quite work. The only way I've gotten it to work is to break up the values in the parameter into something like a table var or a temp table, which you can then use with either an IN clause or a Join.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Dynamic IN Clause

    Ah so something like split the string into an in memory table and join that on to my target table.

    Thanks Ill give it a try.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Dynamic IN Clause

    I think this is clean and simple. No join either. Thanks for pointing me into the right direction TG!

    http://www.codeproject.com/Tips/5846...r-strings-in-S
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [RESOLVED] Dynamic IN Clause

    My resulting new code

    Code:
    DECLARE @user_id int 
    DECLARE @perm_ids varchar(max)
    SET @perm_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76'
    SET @user_id = 1372
    
    SELECT   
    	U.user_id,   
    	U.first_name,   
    	U.last_name,   
    	UP.permission_id,   
    	P.permission_constant,   
    	P.permission_description  
    FROM   
    	[tblUserPermission] UP WITH (NOLOCK)  
    INNER JOIN   
    	tblUser U WITH (NOLOCK) ON UP.user_id = U.user_id  
    INNER JOIN   
    	tblPermission P WITH (NOLOCK) ON UP.permission_id = P.permission_id  
    WHERE   
    	U.User_Id = @user_id  
    		AND
    	UP.permission_id IN (SELECT * FROM dbo.fn_CSVToTable(@perm_ids))
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: [RESOLVED] Dynamic IN Clause

    Actually, because you need to search in a string, you have to use CHARINDEX, like this:

    Code:
    DECLARE @user_id int 
    DECLARE @perm_ids varchar(max)
    SET @perm_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40'
    SET @user_id = 1372
    
    SELECT   
    	U.user_id,   
    	U.first_name,   
    	U.last_name,   
    	UP.permission_id,   
    	P.permission_constant,   
    	P.permission_description  
    FROM   
    	[tblUserPermission] UP WITH (NOLOCK)  
    INNER JOIN   
    	tblUser U WITH (NOLOCK) ON UP.user_id = U.user_id  
    INNER JOIN   
    	tblPermission P WITH (NOLOCK) ON UP.permission_id = P.permission_id  
    WHERE   
    	U.User_Id = @user_id  
    		AND
    	CHARINDEX(',' + CAST(UP.permission_id AS VARCHAR(10)) + ',', ',' + @perm_ids + ',')
    It should be faster than parsing the string in a function, and then calling it in the "IN" statement...

  7. #7

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [RESOLVED] Dynamic IN Clause

    Interesting option. So CHARINDEX is like an Instr function and returns a 0 if not found and > 0 if found.

    So it would be like...
    Code:
    --
    --
    WHERE   
    	U.User_Id = @user_id  
    		AND
    	CHARINDEX(',' + CAST(UP.permission_id AS VARCHAR(10)) + ',', ',' + @perm_ids + ',') > 0
    I too would think that an intrinsic function call would be faster than the custom function returning a table.

    Thanks!
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: [RESOLVED] Dynamic IN Clause

    it's trickery! trickery I tells ya! and yet... if it works, then it works. Not sure I would have thought to use string manipulation like that.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [RESOLVED] Dynamic IN Clause

    Trickery? What ever happend to the "cake"? I guess I havent posted in a looong time lol.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: [RESOLVED] Dynamic IN Clause

    Another way is to run your query as a string query, but I know not many like this method:
    Code:
    DECLARE @user_id int 
    DECLARE @perm_ids varchar(max)
    
    SET @perm_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40'
    SET @user_id = 1372
    
    DECLARE @SQL NVARCHAR(MAX)
    
    SET @SQL = N'
    SELECT
    	U.user_id,   
    	U.first_name,   
    	U.last_name,   
    	UP.permission_id,   
    	P.permission_constant,   
    	P.permission_description  
    FROM   
    	[tblUserPermission] UP WITH (NOLOCK)  
    INNER JOIN   
    	tblUser U WITH (NOLOCK) ON UP.user_id = U.user_id  
    INNER JOIN   
    	tblPermission P WITH (NOLOCK) ON UP.permission_id = P.permission_id  
    WHERE   
    	U.User_Id = @user_id  
    		AND
    	UP.permission_id IN (' + @perm_ids + N')'
    
    EXEC (@SQL)

  11. #11
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: [RESOLVED] Dynamic IN Clause

    While we are at it Here is another way where it's converting the comma separated string to XML, and then converts to a table, and joins with the values:
    Code:
    DECLARE @user_id int 
    DECLARE @perm_ids varchar(max)
    
    SET @perm_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40'
    SET @user_id = 1372
    
    DECLARE @myXML XML
    SET @myXML = '<i>' + REPLACE(@perm_ids, ',', '</i><i>') + '</i>'
    
    --SELECT data.value('(./text())[1]', 'int') AS CountryID
    --FROM @myXML.nodes('/i') R(data)
    
    
    SELECT
    	U.user_id,   
    	U.first_name,   
    	U.last_name,   
    	UP.permission_id,   
    	P.permission_constant,   
    	P.permission_description  
    FROM   	[tblUserPermission] UP WITH (NOLOCK)  
    INNER JOIN 	tblUser U WITH (NOLOCK) ON UP.user_id = U.user_id  
    INNER JOIN 	tblPermission P WITH (NOLOCK) ON UP.permission_id = P.permission_id  
    INNER JOIN	@myXML.nodes('/i') R(data)
    	ON data.value('(./text())[1]', 'int') = UP.permission_id
    WHERE   
    	U.User_Id = @user_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width