Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?
For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it.
SQL Server 2005 is my only applicable limitation I think.
create procedure getDepartments @DepartmentIds varchar(max) as declare @Sql varchar(max) select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')' exec(@Sql)
This question is tagged with
~ Asked on 2008-09-04 06:27:26
Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.
There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons.
I really can't recommend enough to read the article to learn about the tradeoffs among all these options.
~ Answered on 2008-09-04 13:32:34
Yeah, your current solution is prone to SQL injection attacks.
The best solution that I've found is to use a function that splits text into words (there are a few posted here, or you can use this one from my blog) and then join that to your table. Something like:
SELECT d.[Name] FROM Department d JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId
~ Answered on 2008-09-04 06:55:28
You could use XML.
declare @xmlstring as varchar(100) set @xmlstring = '<args><arg value="42" /><arg2>-1</arg2></args>' declare @docid int exec sp_xml_preparedocument @docid output, @xmlstring select [id],parentid,nodetype,localname,[text] from openxml(@docid, '/args', 1)
The command sp_xml_preparedocument is built in.
This would produce the output:
id parentid nodetype localname text 0 NULL 1 args NULL 2 0 1 arg NULL 3 2 2 value NULL 5 3 3 #text 42 4 0 1 arg2 NULL 6 4 3 #text -1
which has all (more?) of what you you need.
~ Answered on 2008-09-04 07:05:13
One method you might want to consider if you're going to be working with the values a lot is to write them to a temporary table first. Then you just join on it like normal.
This way, you're only parsing once.
It's easiest to use one of the 'Split' UDFs, but so many people have posted examples of those, I figured I'd go a different route ;)
This example will create a temporary table for you to join on (#tmpDept) and fill it with the department id's that you passed in. I'm assuming you're separating them with commas, but you can -- of course -- change it to whatever you want.
IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL BEGIN DROP TABLE #tmpDept END SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','') CREATE TABLE #tmpDept (DeptID INT) DECLARE @DeptID INT IF IsNumeric(@DepartmentIDs)=1 BEGIN SET @[email protected] INSERT INTO #tmpDept (DeptID) SELECT @DeptID END ELSE BEGIN WHILE CHARINDEX(',',@DepartmentIDs)>0 BEGIN SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1) SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs)) INSERT INTO #tmpDept (DeptID) SELECT @DeptID END END
This will allow you to pass in one department id, multiple id's with commas in between them, or even multiple id's with commas and spaces between them.
So if you did something like:
SELECT Dept.Name FROM Departments JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID ORDER BY Dept.Name
You would see the names of all of the department IDs that you passed in...
Again, this can be simplified by using a function to populate the temporary table... I mainly did it without one just to kill some boredom :-P
-- Kevin Fairchild
~ Answered on 2008-09-04 16:36:51
A superfast XML Method, if you want to use a stored procedure and pass the comma separated list of Department IDs :
Declare @XMLList xml SET @XMLList=cast('<i>'+replace(@DepartmentIDs,',','</i><i>')+'</i>' as xml) SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))
All credit goes to Guru Brad Schulz's Blog
~ Answered on 2013-03-03 22:19:36
Try This One:
@list_of_params varchar(20) -- value 1, 2, 5, 7, 20 SELECT d.[Name] FROM Department d where @list_of_params like ('%'+ CONVERT(VARCHAR(10),d.Id) +'%')
~ Answered on 2012-10-31 15:12:00