接受多个ID值的t-sql存储过程

Question

接受多个ID值的t-sql存储过程

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

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

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

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

Answer 1

是的，当前的解决方案容易受到SQL注入攻击。我发现的最好的解决方案是使用一个将文本分解成文字的函数(这里有几个，或者您可以使用这是我博客里的)然后将其加入到您的桌子上。类似于：SELECT d.[Name]FROM Department d    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId

Answer 2

你可以使用XML。例如：declare&nbsp;@xmlstring&nbsp;as&nbsp;&nbsp;varchar(100)&nbsp;set&nbsp;@xmlstring&nbsp;=&nbsp;'<args><arg&nbsp;value="42"&nbsp;/><arg2>-1</arg2></args>'&nbsp;declare&nbsp;@docid&nbsp;int&nbsp; exec&nbsp;sp_xml_preparedocument&nbsp;@docid&nbsp;output,&nbsp;@xmlstringselect&nbsp;&nbsp;[id],parentid,nodetype,localname,[text]from&nbsp;&nbsp;&nbsp;&nbsp;openxml(@docid,&nbsp;'/args',&nbsp;1)命令sp_xml_prearedocument是内置的。这将产生产出：id&nbsp; parentid&nbsp; &nbsp; nodetype&nbsp; &nbsp; localname&nbsp; &nbsp;text0&nbsp; &nbsp;NULL&nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;args&nbsp; &nbsp; &nbsp; &nbsp; NULL2&nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;arg&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL3&nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;value&nbsp; &nbsp; &nbsp; &nbsp;NULL5&nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#text&nbsp; &nbsp; &nbsp; &nbsp;424&nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;arg2&nbsp; &nbsp; &nbsp; &nbsp; NULL6&nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#text&nbsp; &nbsp; &nbsp; &nbsp;-1哪个有(更多？)你需要的东西。