Switch Case statement in t-sql

Question

Post reply

Switch Case statement in t-sql

nns80

Mr or Mrs. 500

Points: 524
More actions
January 22, 2004 at 4:34 pm

#161396

Hi,
I would like to know if there is a way to use the switch case statement in t-sql. I cannot use the case statement with a select statement since it involves more than one variable. The example of wht i want using if..else is as given below.
if (@a = 0)       set @test1 = 'xyz'
if (@a = 1)       set @test2 = 'abc'
if (@a = 2)       set @test3 = 'lmn'
Thanks for your help.
-NS

Viewing 6 posts - 1 through 5 (of 5 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

SET @test1 = (SELECT CASE WHEN @a = 0 THEN 'XYZ')

SET @test2 = (SELECT CASE WHEN @a = 1 THEN 'ABC')

SET @test3 = (SELECT CASE WHEN @a = 2 THEN 'LMN')

This is the easiest way to do it. If there are any better ways (which I am sure the guys here can post) but, this will will for you.

1 CASE WHEN THEN ELSE END per @var

Good Luck

Good Hunting!

AJ Ahrens

webmaster@kritter.net

gljjr SSChampion Points: 10080 More actions · Answer 2

Actually I think your multiple IF statements are easier to read. But...

SELECT @test1 = case convert(varchar,@a) WHEN '0' then 'XYZ' END

, @test2 = CASE convert(varchar,@a) WHEN '1' THEN 'abc' END

, @test3 = CASE convert(varchar,@a) WHEN '2' THEN 'lmn' END

I'm pretty sure that you can't change the datatype from int to varchar when inside the case statement, all parts must be the same datatype.

Sorry AJ but I think your syntax is slightly off...

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

SELECT @test1 = CASE @a WHEN 0 then 'xyz' ELSE @test1 END, @test2 = CASE @a WHEN 1 then 'abc' ELSE @test2 END, @test3 = CASE @a WHEN 2 then 'lmn' ELSE @test3 END

--Jonathan

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

No offense taken Gary .

Thanks for the corrections

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Just a few notes on CASE to avoid confusion..

Note that T-SQL CASE is not a Switch statement, it is an Expression that only does one of three things.

It resolves to either TRUE, FALSE or NULL. That's it.

(this is not the same thing as other languages Switch Case statements)

About datatypes, just remember that it is possible to have different datatypes within the Case , but it's not recommended.

The reason is that a Case can only return one datatype, and it will be the one with the highest precedence.

Should you have different dt's, and a value is encountered that is not implicitly convertible with the datatype returned,

an error will occur.

=;o)

/Kenneth