By JasonS


2008-09-04 06:27:26 8 Comments

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)

6 comments

@Unsliced 2008-09-04 07:05:13

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.

@Portman 2008-09-04 13:32:34

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.

@user1313560 2012-10-31 15:12:00

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)  +'%')

very simple.

@Sebastian Meine 2012-10-31 15:28:35

very simple - and very wrong. But even if you would fix the issue in your code it would be very slow. See the "Really Slow Methods" link in the accepted answer for details.

@Nishant 2013-03-03 22:19:36

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

@Kevin Fairchild 2008-09-04 16:36:51

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

@Matt Hamilton 2008-09-04 06:55:28

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

@Anthony 2009-10-08 14:22:57

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.

@MattSlay 2016-11-29 11:58:32

Worked. Like. A. Charm. Thanks!

@Darkloki 2017-05-24 19:14:39

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 "

Related Questions

Sponsored Content

39 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

24 Answered Questions

4 Answered Questions

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

25 Answered Questions

37 Answered Questions

9 Answered Questions

[SOLVED] How to pass an array into a SQL Server stored procedure

Sponsored Content