Why does EXEC retport an error of MUST DECLARE SCALAR VARIABLE

Question

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

Answer 1

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}