SQL Server - 带有声明变量的 In 子句

Question

这个问题已经有答案了:

Parameterize an SQL IN clause (41 个回答)

已关闭 8 年前。

假设我得到以下内容:

DECLARE @ExcludedList VARCHAR(MAX)

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

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

错误:将 varchar 值“,”转换为数据类型 int 时转换失败。

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

Answer 1

This is an example where I use the table variable to list multiple values in an IN clause. The obvious reason is to be able to change the list of values only one place in a long procedure.

To make it even more dynamic and alowing user input, I suggest declaring a varchar variable for the input, and then using a WHILE to loop trough the data in the variable and insert it into the table variable.

用真实的东西替换@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' )