-
Jan 13th, 2014, 12:59 PM
#1
[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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 13th, 2014, 01:13 PM
#2
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
-
Jan 13th, 2014, 01:18 PM
#3
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 13th, 2014, 01:39 PM
#4
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 13th, 2014, 01:48 PM
#5
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 14th, 2014, 12:30 PM
#6
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...
-
Jan 14th, 2014, 12:48 PM
#7
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 14th, 2014, 03:11 PM
#8
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
-
Jan 14th, 2014, 03:43 PM
#9
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jan 14th, 2014, 03:59 PM
#10
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)
-
Jan 14th, 2014, 04:11 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|