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

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

#1 楼

在过去的16年中，Erland Sommarskog一直保持对这个问题的权威性答案：SQL Server中的数组和列表。每个人都有自己独特的优点和缺点。



表值参数。仅限于SQL Server 2008和更高版本，并且可能是最接近通用的“最佳”方法。

迭代方法。传递定界字符串并循环通过它。

使用CLR。 SQL Server 2005及更高版本（仅.NET语言）。

XML。非常适合插入许多行；可能对SELECT来说太过分了。

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

定长元素。固定长度提高了定界字符串的速度。

数字功能。数字表和固定长度的变体，其中数字是在函数中生成而不是从表中获取。

递归公用表表达式（CTE）。 SQL Server 2005及更高版本，但仍比迭代方法不太复杂且性能更高。

动态SQL。可能很慢，并且具有安全隐患。
将尽可能多的参数传递给列表。乏味且容易出错，但简单。

真的很慢的方法。使用charindex，patindex或LIKE的方法。

我真的不建议阅读本文以了解所有这些选项之间的权衡。

#2 楼

是的，您当前的解决方案很容易受到SQL注入攻击。然后从您的表格中加入）。像这样的东西：

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

#3 楼

如果要大量使用这些值，您可能要考虑的一种方法是先将它们写入临时表。然后，您就可以像平常一样加入它。

这样，您只需解析一次。人们已经发布了这些示例，我想我会走一条不同的路线;）

这个示例将创建一个临时表供您加入（#tmpDept），并用部门ID您传入了。我假设您要用逗号分隔它们，但是您可以-当然-可以将其更改为所需的任何内容。将允许您传入一个部门ID，多个ID之间使用逗号，甚至多个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的名称...

再次，可以通过使用函数来填充临时桌子...我主要是为了消除无聊而做的：-P

-Kevin Fairchild

#4 楼

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

，它具有您所需要的全部（更多？）。

#5 楼

超快的XML方法，如果您要使用存储过程并传递以逗号分隔的Department ID列表：

/>

#6 楼

试试这个：

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

非常简单。