This code is throwing error

Question

This code is throwing error

Priya004

Ten Centuries

Points: 1368
More actions
August 18, 2014 at 12:34 am

#298475

declare @b-2 int
declare @action varchar(100)
declare @dbname varchar(100)
declare @a int
set @dbname = 'adt'
set @action = 'dfgf'
set @a = 14
set @b-2 =12
print('select case when '+ @action+ ' = ''view'''+
'then '+@a+' else '+ @b-2+' end')

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Since you are mixing integer data types with string data types, the + operator is not used for string concatenation but for the mathematical addition.

And you can't "add" a string to an integer.

You need to convert the integers to strings.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Shiva N Hall of Fame Points: 3125 More actions · Answer 2

Hi Try this:

Print('select case when '''+@action+'''='''+@dbname+''' then '+cast(@a as varchar)+' else '+cast(@b as varchar)+' end')

Thanks,
Shiva N
Database Consultant

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Shiva N (8/18/2014)
Hi Try this:
Print('select case when '''+@action+'''='''+@dbname+''' then '+cast(@a as varchar)+' else '+cast(@b as varchar)+' end')

I consider leaving out length specifications for strings (and leaving it up to the default) a bad practice.

PRINT('SELECT CASE WHEN ''' + @action + '''=''' + @dbname + ''' THEN' + CAST(@a AS VARCHAR(10)) + ' ELSE' + CAST(@b AS VARCHAR(10))+' END')

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 4

Quick thought, why going through all this trouble with the case statement in the dynamic sql?

😎

declare @b-2 int

declare @action varchar(100)

declare @dbname varchar(100)

declare @a int

set @dbname = 'adt'

set @action = 'dfgf'

set @a = 14

set @b-2 = 12

SELECT

CASE WHEN @dbname = @action THEN N'SELECT ' + CONVERT(VARCHAR(12),@a,3)

ELSE N'SELECT ' + CONVERT(VARCHAR(12),@b,3)

END

Priya004 Ten Centuries Points: 1368 More actions · Answer 5

@Action already set in quotes ,then why od we need to have quotes again the dynamic query?

Priya004 Ten Centuries Points: 1368 More actions · Answer 6

Koen Verbeeck (8/18/2014)
Shiva N (8/18/2014)
Hi Try this:
Print('select case when '''+@action+'''='''+@dbname+''' then '+cast(@a as varchar)+' else '+cast(@b as varchar)+' end')
I consider leaving out length specifications for strings (and leaving it up to the default) a bad practice.
PRINT('SELECT CASE WHEN ''' + @action + '''=''' + @dbname + ''' THEN' + CAST(@a AS VARCHAR(10)) + ' ELSE' + CAST(@b AS VARCHAR(10))+' END')

This is still throwng error saying inccorect syntax near CAST.. instead o fprint try with EXEC it throws error

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

This works fine:

DECLARE @b-2 INT;

DECLARE @action VARCHAR(100);

DECLARE @dbname VARCHAR(100);

DECLARE @a INT;

SET @dbname = 'adt';

SET @action = 'dfgf';

SET @a = 14;

SET @b-2 =12;

PRINT('SELECT CASE WHEN ''' + @action + '''=''' + 'view' + ''' THEN ' + CAST(@a AS VARCHAR(10)) + ' ELSE ' + CAST(@b AS VARCHAR(10))+' END');

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Priya004 Ten Centuries Points: 1368 More actions · Answer 8

Koen Verbeeck (8/18/2014)
This works fine:
DECLARE @b-2 INT;
DECLARE @action VARCHAR(100);
DECLARE @dbname VARCHAR(100);
DECLARE @a INT;
SET @dbname = 'adt';
SET @action = 'dfgf';
SET @a = 14;
SET @b-2 =12;
PRINT('SELECT CASE WHEN ''' + @action + '''=''' + 'view' + ''' THEN ' + CAST(@a AS VARCHAR(10)) + ' ELSE ' + CAST(@b AS VARCHAR(10))+' END');

Oh nooooooo this is throwing error dnt print it try executing it..USE EXEC instead of print

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Indu-649576 (8/18/2014)
Koen Verbeeck (8/18/2014)
This works fine:
DECLARE @b-2 INT;
DECLARE @action VARCHAR(100);
DECLARE @dbname VARCHAR(100);
DECLARE @a INT;
SET @dbname = 'adt';
SET @action = 'dfgf';
SET @a = 14;
SET @b-2 =12;
PRINT('SELECT CASE WHEN ''' + @action + '''=''' + 'view' + ''' THEN ' + CAST(@a AS VARCHAR(10)) + ' ELSE ' + CAST(@b AS VARCHAR(10))+' END');
Oh nooooooo this is throwing error dnt print it try executing it..USE EXEC instead of print

That's because you don't declare the variables in your dynamic SQL. EXEC creates a new execution context.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Priya004 Ten Centuries Points: 1368 More actions · Answer 10

Koen Verbeeck (8/18/2014)
Indu-649576 (8/18/2014)
Koen Verbeeck (8/18/2014)
This works fine:
DECLARE @b-2 INT;
DECLARE @action VARCHAR(100);
DECLARE @dbname VARCHAR(100);
DECLARE @a INT;
SET @dbname = 'adt';
SET @action = 'dfgf';
SET @a = 14;
SET @b-2 =12;
PRINT('SELECT CASE WHEN ''' + @action + '''=''' + 'view' + ''' THEN ' + CAST(@a AS VARCHAR(10)) + ' ELSE ' + CAST(@b AS VARCHAR(10))+' END');
Oh nooooooo this is throwing error dnt print it try executing it..USE EXEC instead of print
That's because you don't declare the variables in your dynamic SQL. EXEC creates a new execution context.

So I have to declare variables in side the dynamic query???correct the sqlcode and plz post it to get executed

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Because you can't concatenate within an EXEC

DECLARE @b-2 INT;

DECLARE @action VARCHAR(100);

DECLARE @dbname VARCHAR(100);

DECLARE @a INT;

SET @dbname = 'adt';

SET @action = 'dfgf';

SET @a = 14;

SET @b-2 =12;

DECLARE @Result VARCHAR(500);

SET @Result = 'SELECT CASE WHEN ''' + @action + '''=''' + 'view' + ''' THEN ' + CAST(@a AS VARCHAR(10)) + ' ELSE ' + CAST(@b AS VARCHAR(10))+' END';

EXEC (@Result);

Should have mentioned you wanted to EXEC it earlier....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass