T-SQL stored procedure that accepts multiple Id values
sql-server, stored-procedures, t-sql
Solution
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.
- Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.
- The Iterative Method. Pass a delimited string and loop through it.
- Using the CLR. SQL Server 2005 and higher from .NET languages only.
- XML. Very good for inserting many rows; may be overkill for SELECTs.
- Table of Numbers. Higher performance/complexity than simple iterative method.
- Fixed-length Elements. Fixed length improves speed over the delimited string
- Function of Numbers. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.
- Recursive Common Table Expression (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.
- Dynamic SQL. Can be slow and has security implications.
- Passing the List as Many Parameters. Tedious and error prone, but simple.
- Really Slow Methods. Methods that uses charindex, patindex or LIKE.
I really can't recommend enough to read the article to learn about the tradeoffs among all these options.
Problem
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) ```