SQL Server - 带有声明变量的 In 子句 [英] SQL Server - In clause with a declared variable

问题描述

假设我得到了以下内容:

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

这篇关于SQL Server - 带有声明变量的 In 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！