接受多个 Id 值的 T-SQL 存储过程 [英] T-SQL stored procedure that accepts multiple Id values

问题描述

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

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

例如，我希望我的存储过程返回部门 1、2、5、7、20.过去，我传递了一个逗号分隔的 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.

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

SQL Server 2005 is my only applicable limitation I think.

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

推荐答案

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

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in 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.

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

• Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.
• The Iterative Method. Pass a delimited string and loop through it.
• Using the CLR. SQL Server 2005 and higher from .NET languages only.
• XML. Very good for inserting many rows; may be overkill for SELECTs.
• 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.
• Dynamic SQL. Can be slow and has security implications.
• Passing the List as Many Parameters. Tedious and error prone, but simple.
• Really Slow Methods. Methods that uses charindex, patindex or LIKE.

我真的不能推荐足够阅读文章来了解所有这些选项之间的权衡.

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

这篇关于接受多个 Id 值的 T-SQL 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！