存储过程可以具有要在“ IN”服务器中使用的动态参数吗？条款？ [英] Can a stored procedure have dynamic parameters to be used in an "IN" clause?

问题描述

我想运行这样的查询：

 SELECT * FROM Studio WHERE Id IN (134, 144, 132, 138, 7432, 7543, 2566)

，但 Id的金额 的传递仅在运行时确定。

but the amount of Id's passed to the IN clause is only determined at runtime.

我必须使用动态SQL还是可以使用存储过程来完成？

Do I have to use dynamic SQL or can this be done with a stored procedure?

更新：
如果有一个选项可用，哪个更好？

UPDATE: If either option is available, which one is better?

谢谢。

推荐答案

根据您的Sql Server版本，您可以使用以下两种不同的方法之一。

Depending on your version of Sql Server, you can do this one of two different ways.

对于Sql 2000/2005，可以使用带有定界ID列表的参数（类型varchar）。创建一个将解析varchar并返回包含项的表的UDF。然后使您的IN子句与该表相反（即... IN（从@ReturnTable选择ID））。

For Sql 2000/2005, you can use a parameter (type varchar) that has a delimited list of IDs. Create a UDF that would parse the varchar and return a table containing the items. Then make your IN clause go against the table (i.e. ...IN (Select ID FROM @ReturnTable)).

以下是UDF内容的示例看起来像：
http： //pietschsoft.com/post/2006/02/03/T-SQL-Parse-a-delimited-string.aspx

Here's an example of what the contents of the UDF would look like: http://pietschsoft.com/post/2006/02/03/T-SQL-Parse-a-delimited-string.aspx

对于Sql 2008，你可以做同样的事情；但是，除了传递varchar参数外，您还可以直接切入并传递Table参数。 IN子句仍将有一个子查询，但它将完全相同。或者，一旦有了该表，就可以对其进行内部联接并避免使用IN子句。

For Sql 2008, you can do the same thing; however instead of passing in a varchar parameter you can just cut to the chase and pass in a Table parameter. The IN clause would still have a subquery but it would work all the same. Alternatively, once you have the table you can just do an Inner Join on it and circumvent the need for the IN clause.

编辑：添加了用于解析定界字符串链接的UDF。

added UDF for parsing a delimited string link.

这篇关于存储过程可以具有要在“ IN”服务器中使用的动态参数吗？条款？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！