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

是否有一种优雅的方法来处理将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)

Portman.. 232

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

至少有十几种方法可以将数组或列表传递给查询; 每个人都有自己独特的优点和缺点.

表值参数.仅限SQL Server 2008及更高版本,可能最接近通用的"最佳"方法.

迭代法.传递一个分隔的字符串并循环遍历它.

使用CLR.仅限.NET语言的SQL Server 2005及更高版本.

XML.非常适合插入许多行; SELECTs可能有点矫枉过正.

数字表.比简单的迭代方法更高的性能/复杂性.

定长元素.固定长度可提高分隔字符串的速度

数字的功能.数字表和固定长度的变化,其中数字在函数中生成而不是从表中生成.

递归公用表表达式(CTE).SQL Server 2005及更高版本,仍然没有比迭代方法更复杂和更高的性能.

动态SQL.可能很慢并且具有安全隐患.

将列表作为多个参数传递.繁琐且容易出错,但很简单.

真的很慢的方法.使用charindex,patindex或LIKE的方法.

我真的不能建议阅读这篇文章来了解所有这些选项之间的权衡.

1> Portman..：

---

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

至少有十几种方法可以将数组或列表传递给查询; 每个人都有自己独特的优点和缺点.

表值参数.仅限SQL Server 2008及更高版本,可能最接近通用的"最佳"方法.

迭代法.传递一个分隔的字符串并循环遍历它.

使用CLR.仅限.NET语言的SQL Server 2005及更高版本.

XML.非常适合插入许多行; SELECTs可能有点矫枉过正.

数字表.比简单的迭代方法更高的性能/复杂性.

定长元素.固定长度可提高分隔字符串的速度

数字的功能.数字表和固定长度的变化,其中数字在函数中生成而不是从表中生成.

递归公用表表达式(CTE).SQL Server 2005及更高版本,仍然没有比迭代方法更复杂和更高的性能.

动态SQL.可能很慢并且具有安全隐患.

将列表作为多个参数传递.繁琐且容易出错,但很简单.

真的很慢的方法.使用charindex,patindex或LIKE的方法.

我真的不能建议阅读这篇文章来了解所有这些选项之间的权衡.

2> Matt Hamilto..：

---

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

我发现的最好的解决方案是使用一个将文本分成单词的功能(这里有一些发布,或者你可以在我的博客中使用这个),然后将其加入到你的表中.就像是:

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

---

我不确定它是否"容易受到SQL注入攻击",除非存储的proc可以直接从不受信任的客户端调用,在这种情况下你会遇到更大的问题.服务层代码应该从强类型数据生成@DepartmentIds字符串(例如int [] departmentIds),在这种情况下你会没事的.