SQL Server - 带有声明变量的 In 子句
问题描述：
假设我得到了以下内容:
Let say I got the following :
DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22'
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)
错误:将 varchar 值,"转换为数据类型 int 时转换失败.
Error : Conversion failed when converting the varchar value ', ' to data type int.
我明白为什么会出现错误，但我不知道如何解决它...
I understand why the error is there but I don't know how to solve it...
答
你需要像动态 sp 一样执行这个
You need to execute this as a dynamic sp like
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