通过存储过程传递“in”列表[duplicate]

This question already has an answer here:

这个问题在这里已有答案：

T-SQL stored procedure that accepts multiple Id values 6 answers
T-SQL存储过程接受多个Id值6个答案

How can I construct a stored procedure that will allow me to pass (for example) an @IDList so that I can write:

如何构造一个允许我传递（例如）@IDList的存储过程，以便我可以编写：

Select * from Foo Where ID in @IDList

Is this doable?

这可行吗？

3 个解决方案

#1

see this answer...

看到这个答案......

T-SQL stored procedure that accepts multiple Id values

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

#2

With SQL2005 and above you can send an array from code directly.

使用SQL2005及更高版本，您可以直接从代码发送数组。

First create a custom type

首先创建一个自定义类型

CREATE TYPE Array AS table (Item varchar(MAX))

Than the stored procedure.

比存储过程。

CREATE PROCEDURE sp_TakeArray
    @array AS Array READONLY
AS BEGIN
    Select * from Foo Where ID in (SELECT Item FROM @array)
END

Then call from code passing in a DataTable as the array

然后从代码传入DataTable作为数组调用

DataTable items = new DataTable();
items.Columns.Add( "Item", typeof( string ) );

DataRow row = items.NewRow();
row.SetField<string>( "Item", <item to add> );
items.Rows.Add( row );

SqlCommand command = new SqlCommand( "sp_TakeArray", connection );
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = command.Parameters.Add( "@Array", SqlDbType.Structured );
param.Value = items;
param.TypeName = "dbo.Array";

SqlDataReader reader = command.ExecuteReader();

#3

Write the individual IDs to table B, all with the same "key" (a GUID perhaps).
Then, your query against table A would include

将单个ID写入表B，所有ID都具有相同的“密钥”（可能是GUID）。然后，您对表A的查询将包括

where ID in (select ID from B where key = @TempKey)

(You might then delete the keys if you are finished with them. Or, timestamp them and have a sql job do it later.)

（如果你已经完成了它们，你可以删除这些键。或者，给它们加上时间戳并让你的SQL作业稍后再做。）

Pros:

优点：

You don't send a string, which could expose you to sql injection in some circumstances.
您不发送字符串，在某些情况下可能会使您暴露于SQL注入。
Depending on your other app logic, you don't have to track or write the possibilities all at once.
根据您的其他应用程序逻辑，您不必一次跟踪或写入所有可能性。

Cons:

缺点：

It could be extremely inefficient, especially under heavy loads.
这可能是非常低效的，特别是在重载下。

#1