T-SQL stored procedure that accepts multiple Id values

Question

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)

Opinionated

Here is a variant of the XML method that I just found. – Opinionated

If you're on SQL Server 2008, you could use a Table-Valued Parameter. http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters – Americium

This was helpful for me: sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure – Sava

Answer 1

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.

Humbertohumble

Answer 2

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

Severe

I'm not sure that it's "prone to SQL injection attacks" unless the stored proc is callable directly from untrusted clients, in which case you have bigger problems. The service layer code should generate the @DepartmentIds string from strongly typed data (e.g. int[] departmentIds), in which case you'll be fine. – Abdel

Awesome solution, @Matt Hamilton. Dont know if this will help anyone, but I got more accurate results on SQL Server 2008r when I was searching text fields by using "join dbo.SplitWords(@MyParameterArray) p ON CHARINDEX(p.value, d.MyFieldToSearch) > 0 " – Baring

Answer 3

You could use XML.

E.g.

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.

Aldredge

Answer 4

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 @DeptID=@DepartmentIDs
    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

Scuppernong

Answer 5

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

Metope