views:

9564

answers:

6

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)
+5  A: 

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
Matt Hamilton
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.
Anthony
+2  A: 

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.

Unsliced
+2  A: 

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

JasonS
+4  A: 

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

Ian Nelson
+24  A: 

Erland Sommarskog has maintained the authoritative answer to this question for the last 12 years: http://www.sommarskog.se/arrays-in-sql.html

It's not worth reproducing all of the options here on StackOverflow, just visit his page and you will learn all you ever wanted to know.

Portman
A: 

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

Kevin Fairchild