接受多个Id值的T-SQL存储过程-Java 学习之路

135

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

例如，我希望我的存储过程返回的部门1,2,5,7,20 . 在过去，我已经通过逗号分隔的id列表，如下面的代码，但感觉真的很脏 .

我认为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 回答

219
过去16年来，Erland Sommarskog一直保持对这个问题的权威答案：Arrays and Lists in SQL Server .

至少有十几种方法可以将数组或列表传递给查询;每个人都有自己独特的优点和缺点 .
- Table-Valued Parameters . 仅限SQL Server 2008及更高版本，可能最接近通用"best"方法 .
- The Iterative Method . 传递一个分隔的字符串并循环遍历它 .
- Using the CLR . 仅限.NET语言的SQL Server 2005及更高版本 .
- XML . 非常适合插入许多行; SELECTs可能有点矫枉过正 .
- Table of Numbers . 比简单的迭代方法更高的性能/复杂性 .
- Fixed-length Elements . 固定长度可提高分隔字符串的速度
- Function of Numbers . 数字表和固定长度的变化，其中数字在函数中生成而不是从表中生成 .
- Recursive Common Table Expression（CTE） . SQL Server 2005及更高版本，仍然没有比迭代方法更复杂和更高的性能 .
- Dynamic SQL . 可能很慢并且具有安全隐患 .
- 将列表作为Many Parameters传递 . 繁琐且容易出错，但很简单 .
- Really Slow Methods . 使用charindex，patindex或LIKE的方法 .
我真的不能建议read the article来了解所有这些选项之间的权衡 .
回复于 2024-04-19T21:09:02+08:00
1
是的，您当前的解决方案容易受到SQL注入攻击 .

我发现的最佳解决方案是使用一个将文本拆分为单词的函数（这里有一些发布，或者您可以使用this one from my blog）然后将其加入到您的表中 . 就像是：
```
SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId
```
回复于 2024-04-19T21:09:02+08:00
3
如果您要使用这些值，可能需要考虑的一种方法是首先将它们写入临时表 . 然后你就像正常一样加入它 .

这样，你只需解析一次 .

最简单的方法是使用其中一个'Split'UDF，但是有很多人发布了这些例子，我想我会走另一条路;）

这个例子将为你创建一个临时表（#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
```
这将允许您传入一个部门ID，多个ID，并在它们之间使用逗号，甚至可以传递多个ID，并在它们之间使用逗号和空格 .

所以如果你做了类似的事情：
```
SELECT Dept.Name 
FROM Departments 
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name
```
您会看到传入的所有部门ID的名称......

再次，这可以通过使用一个函数来填充临时表来简化...我主要做了没有一个只是为了杀死一些无聊:-P
- 凯文费尔柴尔德
回复于 2024-04-19T21:09:02+08:00

你可以使用XML .

例如 .

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)

命令sp_xml_preparedocument是内置的 .

这会产生输出：

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

哪个（你更多？）你需要什么？

回复于 2024-04-19T21:09:02+08:00

-2
一个超高速XML方法，如果要使用存储过程并传递以逗号分隔的部门ID列表：
```
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))
```
所有功劳都归功于Guru Brad Schulz's Blog
回复于 2024-04-19T21:09:02+08:00

试试这个：

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

非常简单 .

回复于 2024-04-19T21:09:02+08:00

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

6 回答

相关问题