是否有一种优美的方法来处理将ID列表作为参数传递给存储过程？
例如，我希望存储过程返回部门1、2、5、7、20。 过去，我像下面的代码一样，以逗号分隔的ID列表进行了传递，但是这样做确实感到很脏。
我认为SQL Server 2005是我唯一适用的限制。
1
2
3
4
5
6
| create procedure getDepartments
@DepartmentIds varchar(max)
as
declare @Sql varchar(max)
select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
exec(@Sql) |
在过去的16年中，Erland Sommarskog始终保持对这个问题的权威答案：SQL Server中的数组和列表。
至少有十二种方法可以将数组或列表传递给查询。每个人都有自己独特的优点和缺点。
-
表值参数。仅限于SQL Server 2008和更高版本，可能是最接近通用的"最佳"方法。
-
迭代方法。传递定界字符串并循环通过它。
-
使用CLR。仅.NET语言的SQL Server 2005及更高版本。
-
XML。非常适合插入许多行；可能对SELECT来说太过分了。
-
数字表。比简单的迭代方法更高的性能/复杂度。
-
定长元素。固定长度提高了定界字符串的速度
-
数字的功能。数字表和固定长度的变体，其中数字是在函数中生成的，而不是从表中获取的。
-
递归公用表表达式(CTE)。 SQL Server 2005及更高版本，仍然没有比迭代方法复杂和更高的性能。
-
动态SQL。可能很慢并且具有安全隐患。
-
将列表作为许多参数传递。乏味且容易出错，但很简单。
-
方法真的很慢。使用charindex，patindex或LIKE的方法。
我真的不建议阅读本文以了解所有这些选项之间的权衡。
是的，您当前的解决方案很容易受到SQL注入攻击。
我找到的最好的解决方案是使用一种将文本拆分为单词的功能(此处有一些张贴内容，或者您??可以从我的博客中使用此功能)，然后将其连接到表中。就像是：
1
2
3
| SELECT d.[Name]
FROM Department d
JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId |
您可能要考虑的一种方法是，首先将它们写入临时表。然后，您只需像平常一样加入即可。
这样，您只需解析一次。
使用其中一种"拆分" UDF最简单，但是有很多人发布了这些示例，我认为我会走另外一条路;)
本示例将创建一个临时表供您加入(#tmpDept)并用您传入的部门ID填充它。我假设您要用逗号分隔它们，但是您当然可以更改随你想要
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| 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 |
这将允许您传入一个部门ID，在多个ID之间使用逗号分隔，甚至在多个ID之间使用逗号和空格传递。
因此，如果您执行以下操作：
1
2
3
4
| SELECT Dept.Name
FROM Departments
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name |
您会看到您传入的所有部门ID的名称...
同样，可以通过使用函数填充临时表来简化此操作...我主要是在没有一个函数的情况下这样做的，只是为了消除一些无聊的事情：
-凯文·费尔柴尔德(Kevin Fairchild)
您可以使用XML。
例如。
1
2
3
4
5
6
7
8
9
| declare @xmlstring as varchar(100)
set @xmlstring = '-1</arg2></args>'
declare @docid int
exec sp_xml_preparedocument @docid output, @xmlstring
select [id],parentid,nodetype,localname,[text]
from openxml(@docid, '/args', 1) |
内置命令sp_xml_preparedocument。
这将产生输出：
1
2
3
4
5
6
7
| 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 |
哪些具有您所需的全部(更多？)。
超高速XML方法，如果要使用存储过程并传递以逗号分隔的Department ID列表：
1
2
3
| Declare @XMLList xml
SET @XMLList=cast(''+replace(@DepartmentIDs,',','')+'' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i)) |
所有功劳归于Guru Brad Schulz的博客
试试这个：
1
2
3
4
5
| @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) +'%') |
很简单的。
