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

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

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

我认为 SQLServer2005是我唯一适用的限制。

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

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

我发现的最佳解决方案是使用一个函数，将文本分割成单词(这里有一些贴出来的，或者您可以使用 这是我博客上的) ，然后将其连接到您的表中。比如:

SELECT d.[Name]
FROM Department d
JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId
小开

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

哪一个有你所需要的全部(更多?)。

小开
最佳答案

Erland Sommarskog 在过去16年中一直对这个问题保持权威的答案: SQL Server 中的数组和列表。

至少有十几种方法可以将数组或列表传递给查询; 每种方法都有其独特的优缺点。

  • 表值参数 . SQL Server 2008及更高版本，可能是最接近通用“最佳”方法的方法。
  • 迭代法 。传递一个带分隔符的字符串并循环通过它。
  • 仅使用. NET 语言中的 CLR . SQLServer2005或更高版本。
  • XML 。非常适合插入多行; 对 SELECT 来说可能有点过了。
  • 数字表 性能/复杂度高于简单的迭代法。
  • 固定长度的元素 。固定长度提高了分隔字符串的速度
  • 数的函数 。数字表的变化和固定长度，其中数字是在函数中生成的，而不是从表中获取的。
  • 递归公共表表达式 (CTE) . SQL Server 2005及更高版本，仍然不太复杂，性能也比迭代法高。
  • 动态 SQL 可能比较慢，并且有安全隐患。
  • 将 List 作为 许多参数传递。冗长且容易出错，但是很简单。
  • 真正慢的方法 使用 charindex、 patindex 或 LIKE 的方法。

我真的不能向 读读这篇文章推荐足够多的东西来了解所有这些选项之间的权衡。

小开

如果要经常使用这些值，您可能需要考虑的一个方法是首先将它们写入一个临时表。然后你就像平常一样加入进来。

这样，您只解析一次。

使用一个“分离”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

你会看到你传进去的所有部门证件的名字。

同样，这可以通过使用一个函数来填充临时表来简化... ... 我这样做主要是为了消除一些无聊:-P

—— Kevin Fairchild

小开

如果希望使用存储过程并传递以逗号分隔的部门 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))

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


提交答案