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

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  January 22, 2004 at 5:14 pm

  #491248

  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

  January 22, 2004 at 8:43 pm

  #491255

  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

  January 23, 2004 at 8:32 am

  #491389

  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

  January 24, 2004 at 5:03 am

  #491496

  No offense taken Gary .

   

  Thanks for the corrections

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 26, 2004 at 2:03 am

  #491569

  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