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}

Answer 2

Since exec is executing dynamic part of SQL which is inside single quote. But @var2 is declared outside exec. To solve this declare and select @var2 inside exec or use @var2 as set '+@var2+'

Answer 3

Use sp_executeSQL instead of exec and use an output parameter

E.g. How to get sp_executesql result into a variable?

For example

DECLARE @var1 as VarChar(50) 
DECLARE @var2 as VarChar(50) 
DECLARE @ParmDefinition nvarchar(500);
SET @Var1 = '1, 2, 3, 4, 5'

SET @ParmDefinition = N'@Var2  VarChar(50) OUTPUT';

DECLARE @sSQL nvarchar(500);

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

EXEC sp_executesql @sSQL, @ParmDefinition, @Var2=@Var2 OUTPUT;

SELECT @Var2;

Answer 4

There are two things there.. first where are the ";" ending the sentences. Also you need to scape the varchar values.

Agree with the other answer... use sp_executeSQL to run the dynamic sql.

An example of declare....

USE AdventureWorks2012;
GO
DECLARE @find varchar(30); 
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%'; 
*/
SET @find = 'Man%'; 
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p 
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find; 

An example of sp_executeSQL..

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

Answer 5

There are many reasons to stay away from executing sql strings. I would rewrite this to be:

declare @cnt int

select @cnt = count(1) 
from Table1 
where Column1 in
    (select items
    from dbo.Split(@Var1,',')
    )

return @cnt   -- then the calling function says if this is true or false.

for the Split function see: HERE