question

pragathi avatar image
pragathi asked

Problem with CASE Statement in T-SQL

Hello Everyone,

I am a beginner trying to learn T-SQL and here is where I was stuck. Can anyone please help me out with this.

use AdventureWorks go

declare @n as int =1

n = CASE

when n=1 then print('n=1')

when n=2 then print ('n=2')

print ('that implies n>1')

when n=2+2 then print('n=4')

else

print('n is some other value.')

end

Thankyou very much.....It would be of great help !!

sql-server-2008sql-server-2005t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

you should be using IF . . ELSE for flow control not CASE .. WHEN

CASE .. WHEN is to use within a QUERY

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Piotr Rodak avatar image
Piotr Rodak answered

You can't mix assignment with output in CASE statement.

You can print the output basing on the value of the @n variable using following syntax for example:

declare @n int             
set @n = 1            
            
print CASE            
    when @n=1 then 'n=1'            
    when @n=2 then 'n=2'            
    when @n=2+2 then 'n=4'            
    else            
    'n is some other value.'            
end            

Please note that each time you use variable it must be prefixed with @ and print statement contains conditional expression that determines what has to be printed. Alternatively, you can assign the output of case statement to a variable and print it:

declare @n int, @msg varchar(50)             
set @n = 1            
            
set @msg = CASE            
        when @n=1 then 'n=1'            
        when @n=2 then 'n=2'            
        when @n=2+2 then 'n=4'            
        else            
        'n is some other value.'            
    end            
            
print @msg            

When you assign a value to a variable, you have to use keyword set or select, depending on the situation.

Regards

Piotr

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.