Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does EXEC retport an error of MUST DECLARE SCALAR VARIABLE

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

like image 641
user2146755 Avatar asked Mar 08 '13 02:03

user2146755


1 Answers

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
like image 60
John Woo Avatar answered Oct 21 '22 04:10

John Woo