从逗号分隔列表创建SQL表

Question

我正在运行SQL Server并且有一个存储过程。我想使用WHERE IN子句进行select语句。我不知道列表的长度，因此现在我尝试了以下内容：

SELECT * FROM table1 WHERE id IN (@idList)

在这个解决方案中，@idList是一个VarChar（max）类型的变量。但是这种方法不起作用。我听说可以传递表格值，但是我对如何做到这一点感到困惑。希望能得到任何帮助。

- jamesatha

---

SQL Server的哪个版本？这里有一些分割函数https://dev59.com/XnRC5IYBdhLWcg3wYP-h，但如果您使用的是SQL2008，则TVPs可能更好。 - Martin Smith

什么是 TVP？（我需要两个字符才能完成这条评论，但这句话可能有点多余） - MisterZimbu

TVP = 表值参数 - Joe Stefanelli

Answer 1

我建议使用一个函数来分割传入的列表（使用马丁在评论中提供的链接）。

将分割函数的结果存储在一个临时表或表变量中，并在查询中加入它，而不是使用WHERE子句。

select * into #ids from dbo.Split(',', @idList)

select t.*
from table1 t
 join #ids i
    on t.id = i.s

- Mike Forman

---

最好使用表变量（如果可能的话），以避免锁定tempdb。 - RyanHennig

Answer 2

如果您使用的是SQL Server 2008，则最有效的方法是传递一个表值参数；如果您使用的是SQL Server 2005/2000，则需要传递一个XML参数。如果您的列表很小（并且您使用的是SQL Server 2005/2000），则可以将列表作为逗号分隔符（或其他方式）传递，并使用拆分函数将值分解为临时表中的行。

无论您选择哪种选项，都需要将此表（无论是表参数、从XML选择结果的表，还是由拆分值创建的临时表）与主查询连接起来。

- Mark

---

虽然这并不重要，但XML参数也适用于SQL Server 2000。 - Conrad Frix

@Conrad：当然，你是正确的，我已经更新了答案来反映这一点。 - Mark

Answer 3

这是一个表值函数，它接受一个 nvarchar 并返回一个可用于连接的表：

Create function [ReturnValues]
(
@Values nvarchar(4000)
)
Returns @ValueTable table(Value nvarchar(2000))
As
Begin
Declare @Start int
Declare @End int
Set @Start = 1
Set @End = 1

While @Start <= len(@Values)
Begin
      Set @End = charindex(',', @Values, @Start)
      If @End = 0
            Set @End = len(@Values) + 1
      Insert into @ValueTable
      Select rtrim(ltrim(substring(@Values, @Start, @End - @Start)))
      Set @Start = @End + 1
End
Return
End

GO

- Decker97

Answer 4

如您所建议的绑定@idList参数在SQL中不可行。

最好将ID批量插入一个单独的表中，然后通过使用子查询或连接这些ID来查询该表。

例如：

INSERT INTO idTable (id, context) values (@idValue, 1);
INSERT INTO idTable (id, context) values (@idValue, 1);
INSERT INTO idTable (id, context) values (@idValue, 1); // as often as you like
SELECT * FROM table1, idTable WHERE table1.id == idTable.id and idTable.context = 1

上下文必须是一个唯一的值，用于标识ID范围。这对于并行运行存储过程非常重要。如果没有上下文信息，同时运行存储过程会混合来自不同选择的值。

- BitKFu

Answer 5

如果参数数量相当小（＜100），则可以使用多个参数。

SELECT * FROM table1 WHERE IN id IN (@id1, @id2, @id3)

如果字符串较长，则可以使用分割函数。

- Albin Sunnanbo