接受多个Id值的T-SQL存储过程

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

是否有一种处理将id列表作为参数传递给存储过程的优雅方法?

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.

例如，我希望存储过程返回部门1、2、5、7、20。在过去，我传递了一个用逗号分隔的id列表，如下面的代码，但是这样做真的很脏。

SQL Server 2005 is my only applicable limitation I think.

SQL Server 2005是我认为唯一适用的限制。

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

6 个解决方案

#1

210

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.

在过去的16年中，Erland Sommarskog一直保持着这个问题的权威答案: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.
表值参数。SQL Server 2008和更高版本，可能是最接近通用的“最佳”方法。
The Iterative Method. Pass a delimited string and loop through it.
迭代方法。传递带分隔符的字符串并对其进行循环。
Using the CLR. SQL Server 2005 and higher from .NET languages only.
使用CLR。SQL Server 2005和更高版本，仅适用于。net语言。
XML. Very good for inserting many rows; may be overkill for SELECTs.
XML。非常适合插入多行;对于选择来说，可能有些过头了。
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.
递归公共表表达式(CTE)。SQL Server 2005和更高版本，仍然没有太复杂和比迭代方法更高的性能。
Dynamic SQL. Can be slow and has security implications.
动态SQL。可能很慢，并且有安全隐患。
Passing the List as Many Parameters. Tedious and error prone, but simple.
将列表作为多个参数传递。乏味且容易出错，但很简单。
Really Slow Methods. Methods that uses charindex, patindex or LIKE.
很慢的方法。使用charindex、patindex或类似的方法。

I really can't recommend enough to read the article to learn about the tradeoffs among all these options.

我真的无法推荐足够多的人阅读这篇文章来了解所有这些选项之间的权衡。

#2

Yeah, your current solution is prone to SQL injection attacks.

是的，您当前的解决方案容易发生SQL注入攻击。

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

#3

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 ;)

使用一个“拆分”的udf是最容易的，但是很多人已经发布了这些例子，我想我应该走另一条路;

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.

这个示例将为您创建一个临时表，以便您加入(#tmpDept)，并使用您传入的部门id填充它。我假设你用逗号把它们分开，但是你可以——当然——把它改成你想要的。

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.

这将允许您传入一个部门id，多个id之间带有逗号，甚至多个id之间带有逗号和空格。

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

你会看到你传入的所有部门id的名称……

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

-- Kevin Fairchild

——凯文仙童

#4

You could use XML.

您可以使用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.

内置命令sp_xml_preparedocument。

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.

它拥有你所需要的一切(更多)。

#5

A superfast XML Method, if you want to use a stored procedure and pass the comma separated list of Department IDs :

如果您想要使用存储过程，并传递部门id的逗号分隔列表，则使用超高速XML方法:

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

所有的功劳都归于布拉德·舒尔茨的博客

#6

-2

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.

非常简单。

#1

210