<p>Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure? </p>

<p>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. </p>

<p>SQL Server 2005 is my only applicable limitation I think.</p>

<pre><code>create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)     
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)
</code></pre>

[{'answer_id': 43260, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 4, 'selected': False, 'text': '<p>Yeah, your current solution is prone to SQL injection attacks.</p>\n\n<p>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 <a href="http://www.madprops.org/blog/splitting-text-into-words-in-sql-revisited/" rel="noreferrer">this one from my blog</a>) and then join that to your table. Something like:</p>\n\n<pre><code>SELECT d.[Name]\nFROM Department d\n    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId\n</code></pre>\n'}, {'answer_id': 43263, 'author': 'Unsliced', 'author_id': 2902, 'author_profile': 'https://Stackoverflow.com/users/2902', 'pm_score': 2, 'selected': False, 'text': '<p>You could use XML. </p>\n\n<p>E.g. </p>\n\n<pre><code>declare @xmlstring as  varchar(100) \nset @xmlstring = \'&lt;args&gt;&lt;arg value="42" /&gt;&lt;arg2&gt;-1&lt;/arg2&gt;&lt;/args&gt;\' \n\ndeclare @docid int \n\nexec sp_xml_preparedocument @docid output, @xmlstring\n\nselect  [id],parentid,nodetype,localname,[text]\nfrom    openxml(@docid, \'/args\', 1) \n</code></pre>\n\n<p>The command <a href="http://msdn.microsoft.com/en-us/library/ms187367.aspx" rel="nofollow noreferrer">sp_xml_preparedocument</a> is built in. </p>\n\n<p>This would produce the output: </p>\n\n<pre><code>id  parentid    nodetype    localname   text\n0   NULL        1           args        NULL\n2   0           1           arg         NULL\n3   2           2           value       NULL\n5   3           3           #text       42\n4   0           1           arg2        NULL\n6   4           3           #text       -1\n</code></pre>\n\n<p>which has all (more?) of what you you need.</p>\n'}, {'answer_id': 43767, 'author': 'Portman', 'author_id': 1690, 'author_profile': 'https://Stackoverflow.com/users/1690', 'pm_score': 9, 'selected': True, 'text': '<p>Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: <em><a href="http://www.sommarskog.se/arrays-in-sql.html" rel="noreferrer">Arrays and Lists in SQL Server</a></em>.</p>\n\n<p>There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons. </p>\n\n<ul>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2008.html" rel="noreferrer">Table-Valued Parameters</a>. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#iterative" rel="noreferrer">The Iterative Method</a>. Pass a delimited string and loop through it.  </li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#CLR" rel="noreferrer">Using the CLR</a>. SQL Server 2005 and higher from .NET languages only. </li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#XML" rel="noreferrer">XML</a>. Very good for inserting many rows; may be overkill for SELECTs.</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum" rel="noreferrer">Table of Numbers</a>. Higher performance/complexity than simple iterative method.</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-length" rel="noreferrer">Fixed-length Elements</a>. Fixed length improves speed over the delimited string</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#fn_nums" rel="noreferrer">Function of Numbers</a>. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#CTEs" rel="noreferrer">Recursive Common Table Expression</a> (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.</li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#dynamic-sql" rel="noreferrer">Dynamic SQL</a>. Can be slow and has security implications. </li>\n<li>Passing the List as <a href="http://www.sommarskog.se/arrays-in-sql-2005.html#manyparameters" rel="noreferrer">Many Parameters</a>. Tedious and error prone, but simple.  </li>\n<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html#realslow" rel="noreferrer">Really Slow Methods</a>. Methods that uses charindex, patindex or LIKE.</li>\n</ul>\n\n<p>I really can\'t recommend enough to <a href="http://www.sommarskog.se/arrays-in-sql.html" rel="noreferrer">read the article</a> to learn about the tradeoffs among all these options.</p>\n'}, {'answer_id': 44136, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 2, 'selected': False, 'text': "<p>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.</p>\n\n<p>This way, you're only parsing once.</p>\n\n<p>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 ;)</p>\n\n<p>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.</p>\n\n<pre><code>IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL\nBEGIN\n    DROP TABLE #tmpDept\nEND\n\nSET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','')\n\nCREATE TABLE #tmpDept (DeptID INT)\nDECLARE @DeptID INT\nIF IsNumeric(@DepartmentIDs)=1\nBEGIN\n    SET @DeptID=@DepartmentIDs\n    INSERT INTO #tmpDept (DeptID) SELECT @DeptID\nEND\nELSE\nBEGIN\n        WHILE CHARINDEX(',',@DepartmentIDs)&gt;0\n        BEGIN\n            SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1)\n            SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs))\n            INSERT INTO #tmpDept (DeptID) SELECT @DeptID\n        END\nEND\n</code></pre>\n\n<p>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.</p>\n\n<p>So if you did something like:</p>\n\n<pre><code>SELECT Dept.Name \nFROM Departments \nJOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID\nORDER BY Dept.Name\n</code></pre>\n\n<p>You would see the names of all of the department IDs that you passed in...</p>\n\n<p>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</p>\n\n<p>-- Kevin Fairchild</p>\n"}, {'answer_id': 15191592, 'author': 'Nishant', 'author_id': 2089165, 'author_profile': 'https://Stackoverflow.com/users/2089165', 'pm_score': 2, 'selected': False, 'text': '<p>A superfast XML Method, if you want to use a stored procedure and pass the comma separated list of Department IDs : </p>\n\n<pre><code>Declare @XMLList xml\nSET @XMLList=cast(\'&lt;i&gt;\'+replace(@DepartmentIDs,\',\',\'&lt;/i&gt;&lt;i&gt;\')+\'&lt;/i&gt;\' as xml)\nSELECT x.i.value(\'.\',\'varchar(5)\') from @XMLList.nodes(\'i\') x(i))\n</code></pre>\n\n<p>All credit goes to Guru <a href="http://beyondrelational.com/modules/2/blogs/114/posts/14617/delimited-string-tennis-anyone.aspx" rel="nofollow">Brad Schulz\'s Blog</a></p>\n'}]
['https://Stackoverflow.com/questions/43249', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1865/']