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: 259155 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: 259155 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: 182946 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: 259155 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: 259155 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

This code is throwing error

Cookies on SQLServerCentral