Why does EXEC retport an error of MUST DECLARE SCALAR VARIABLE
sql, sql-server, t-sql
Solution
You need to use `sp_executesql` if you want to get values from parameters.
To shorten the query, use `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
if you want to know if all `ID` from `@Var1` is present on the table, you also need to use `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
Problem
I've been struggling with the script bellow and I can't figure out a better way to do it. Does anyone see the problem? I'm declaring the variable correctly. Whys is it failing? Thanks for your help! ``` 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 ``` Error Message: MUST DECLARE SCALAR VARIABLE @Var2