sql - 为什么 EXEC 报告 MUST DECLARE SCALAR VARIABLE 错误

Question

我一直在努力处理下面的脚本，但我找不到更好的方法。
有人看到问题所在吗？我正确地声明了变量。为什么会失败呢？感谢您的帮助!

DECLARE @var1 as VarChar(50) 
DECLARE @var2 as VarChar(50) 

SET @Var1 = '1, 2, 3, 4, 5'

EXEC('IF (select count(*) from Table1 where Column1 in (' + @Var1 + ')) = 5 
     SET @Var2 = ''True''
ELSE 
     SET @Var2 = ''False''')

SELECT @Var2

错误消息:

MUST DECLARE SCALAR VARIABLE @Var2

Answer 1

如果您想从参数中获取值，则需要使用sp_executesql。

要缩短查询，请使用CASE。

DECLARE @var1 as VarChar(50) 
DECLARE @var2 as VarChar(50) 
SET @Var1 = '1, 2, 3, 4, 5'
SET @sqlCommand = 'SELECT @Var = CASE WHEN count(*) = 5 THEN ''TRUE'' ELSE ''FALSE'' END FROM Table1 where Column1 IN (' + @Var1 + ')'
EXECUTE sp_executesql @sqlCommand, N'@Var VARCHAR(5) OUTPUT', @Var=@var2 OUTPUT
SELECT @Var2

如果您想知道来自 @Var1 的所有 ID 是否都出现在表格中，您还需要使用 DISTINCT

SET @sqlCommand = 'SELECT @Var = CASE WHEN count(DISTINCT Column1) = 5 THEN ''TRUE'' ELSE ''FALSE'' END FROM Table1 where Column1 IN (' + @Var1 + ')'

• sp_executesql ^{MSDN Docs}