SQL Server - 使用声明的变量进行 IN 子句

Question

假设我有以下内容：

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

错误：将varchar值', '转换为数据类型int时转换失败。

我知道为什么会出现这个错误，但我不知道该如何解决它...

- Melursus

---

@TomTom和其他人 - 我不同意这是一个重复的问题。另一个问题涵盖了与此问题特定内容无关的更多方面。最重要的是，我很高兴找到了这篇文章而不是其他文章 - 因为这篇文章恰好解决了我的问题。 - qxotk

也许你可以使用在 SQL Server 2016 中可用的 string_split 函数，将你的代码更改为 select * from A where Id not In (select value from string_split('3;4;5;6',';'))。 - Guokas

Answer 1

这是一个使用表变量在IN子句中列出多个值的示例。显而易见的原因是能够在长过程中仅更改一个位置的值列表。

为了使它更加动态和允许用户输入，我建议声明一个varchar变量用于输入，然后使用WHILE循环遍历变量中的数据并将其插入到表变量中。

请将@your_list、Your_table和值替换为实际内容。

DECLARE @your_list TABLE (list varchar(25)) 
INSERT into @your_list
VALUES ('value1'),('value2376')

SELECT *  
FROM your_table 
WHERE your_column in ( select list from @your_list )

上面的select语句与以下语句相同：

SELECT *  
FROM your_table 
WHERE your_column in ('value','value2376' )

- Mikkel Tronsrud

---

这将导致性能下降，因为在IN子句中的第二个SELECT将针对每一行执行。 - fatiDev

我正在使用相同的东西，但问题在于如果我在我的“IN”子句中使用表格，它需要大约20秒钟。但是，如果我在那里使用一个字符串，那么它可以在不到2秒钟内运行。因此，我想避免使用临时表并为IN子句创建一个字符串。 - Gautam

Answer 2

你需要将此作为动态存储过程执行

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'

exec sp_executesql @sql

- TonyP

---

如果ExcludedList是由用户输入的，那么这不容易受到SQL注入攻击吗？例如，ExcludedList = '3); DROP TABLE USERS; --'（评论系统不允许我使用@符号）。 - ristonj

没关系。多个变量（每个项目一个）或动态SQL是处理此问题的唯一方法。是的，必须小心谨慎。或者删除in子句（并将项目加载到临时表/表变量中，然后进行连接）。 - TomTom

我更倾向于使用字符串分割器而不是动态SQL来避免SQL注入。http://sqlperformance.com/2012/07/t-sql-queries/split-strings - Sean Lange

这是一个有效的答案，但不是唯一的答案。所有答案都有利弊需要审查 :) - Mike M

如果变量是int类型，这个可以工作。那么如果变量是varchar类型呢？ - Ahmad Pujianto

Answer 3

DECLARE @IDQuery VARCHAR(MAX)
SET @IDQuery = 'SELECT ID FROM SomeTable WHERE Condition=Something'
DECLARE @ExcludedList TABLE(ID VARCHAR(MAX))
INSERT INTO @ExcludedList EXEC(@IDQuery)    
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

我知道我在回复一个旧帖子，但我想分享一个使用变量表的例子，当一个人想要避免使用动态SQL时。 我不确定这是否是最有效的方法，但在以前无法使用动态SQL时，这对我有用过。

- Carl Osterman

---

好的，我会尽力进行翻译。这句话的意思是“我喜欢这个，因为你避免使用动态 SQL”。 - Techgration

我认为 EXEC(@IDQuery) 是动态的？ - David R Tribble

你不一定要这样做，我也不确定他为什么要这样做。你可以像这样做：DECLARE @SomeTableVar TABLE(id INT)INSERT INTO @SomeTableVar SELECT some_int FROM some_tableWHERE some_int IS NOT NULLSELECT * FROM @SomeTableVar我认为这只是一个人为的例子。顺便说一下，对于任何评论回复垃圾邮件，我很抱歉。 我是个大笨蛋，不明白在StackOverflow上，评论标记与答案标记不同。 :v - Elijah Woodward

IN(@variable) 期望是一个标量，而不是一个表变量。 - Sam

Answer 4

你不能在一个IN语句中使用变量，需要使用动态SQL或使用函数（TSQL或CLR）将值列表转换为表格。

动态SQL示例：

DECLARE @ExcludedList VARCHAR(MAX)
    SET @ExcludedList = 3 + ',' + 4 + ',' + '22'

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT * FROM A WHERE Id NOT IN (@ExcludedList) '

 BEGIN

   EXEC sp_executesql @SQL '@ExcludedList VARCHAR(MAX)' @ExcludedList

 END

- OMG Ponies

Answer 5

首先，创建一个快速函数将分隔符列表中的值拆分成表格，如下所示：

CREATE FUNCTION dbo.udf_SplitVariable
(
    @List varchar(8000),
    @SplitOn varchar(5) = ','
)

RETURNS @RtnValue TABLE
(
    Id INT IDENTITY(1,1),
    Value VARCHAR(8000)
)

AS
BEGIN

--Account for ticks
SET @List = (REPLACE(@List, '''', ''))

--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
    SET @List = ''
END

--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN

    INSERT INTO @RtnValue (value)
    SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))  

    SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))

END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END 

然后像这样调用函数...

SELECT * 
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL

这在我们的项目中表现得非常好...

当然，如果情况相反，也可以做相反的事情（尽管这不是你的问题）。

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value

当处理具有可选多选参数列表的报告时，这将非常方便。如果参数为NULL，则选择所有值，但如果它具有一个或多个值，则希望报告数据根据这些值进行过滤。然后使用以下SQL：

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL

这样，如果@ExcludeList是一个NULL值，在连接中的OR子句就会变成一个开关，关闭对该值的过滤。非常方便...

- laughsloudly

---

这是一个绝妙的解决方案。它应该被接受作为答案。它还适用于varchar类型的项目，这在动态SQL答案中变得繁琐。 - done_merson

Answer 6

我认为问题出在

3 + ', ' + 4

将其更改为

'3' + ', ' + '4'

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3' + ', ' + '4' + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

设置@ExcludedList，使您的查询变为

要么

SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')

或者

SELECT * FROM A WHERE Id NOT IN (3, 4, 22)

- Salil

---

那修复了set语句，但是select查询仍然无法工作。 - Joel Coehoorn

这对我非常有效。 - Vikram Shetty

Answer 7

试试这个：

CREATE PROCEDURE MyProc @excludedlist integer_list_tbltype READONLY AS
  SELECT * FROM A WHERE ID NOT IN (@excludedlist)

然后像这样调用它：

DECLARE @ExcludedList integer_list_tbltype
INSERT @ExcludedList(n) VALUES(3, 4, 22)
exec MyProc @ExcludedList

- Makis

Answer 8

我有另一种解决方案，可以不使用动态查询来完成。 我们也可以借助xquery来实现。

    SET @Xml = cast(('<A>'+replace('3,4,22,6014',',' ,'</A><A>')+'</A>') AS XML)
    Select @Xml

    SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

这里是完整的解决方案： http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/

- user1059637