How to use switch - case statement in T-SQL..?


  • Hi,

    I want to use switch - case statement in T-SQL stored procedure.

    Can any one help regarding the same..?


    for e.g.

    switch (exp)


    case 1 : stmt 1; break;

    case 2 : stmt 2; break;

    case 3 : stmt 3; break;

    & so on.......




    Thursday, April 5, 2007 4:04 AM


All replies

  • Hi,

    See the following example


    DECLARE @TestVal int
    SET @TestVal = 3

    CASE @TestVal
    WHEN 1 THEN 'First'
    WHEN 2 THEN 'Second'
    WHEN 3 THEN 'Third'
    ELSE 'Other'

    Thursday, April 5, 2007 6:12 AM
  • I tried this way but dosen't work when i want to calculate or insert or delete

    it can be used to view the result directly without any calculation


    • Proposed as answer by Mephak Thursday, July 1, 2010 9:31 PM
    Thursday, June 24, 2010 8:00 AM
  • Can you please elaborate your requirement so that we can understand properly. You can also use use dynamic sql to achieve what you want..but you just need to be careful when you are playing around with dynamic sql.
    Thursday, June 24, 2010 9:04 AM
  • Hi,

    Try something like this....

    Insert into mytableB
    Select YEAR,
    case when PERIOD in (1,2,3) then Q1
            when PERIOD in (4,5,6) then Q2
            when PERIOD in (7,8,9) then Q3
           Else Q4 end ,
    From myTableA
    Where Year = 2010
    and Period = 5


    Insert into mySalesB
    Select SalesPerson,
    case when Sales < 10000 then Comm * 1.5
            when Sales < 20000 then Comm * 2.5
           Else Comm * 4 end ,
    Region ,getdate()
    From mySalesA


    • Proposed as answer by Mephak Thursday, July 1, 2010 9:43 PM
    Thursday, July 1, 2010 9:43 PM
  • I know this is 3 years old, But its number 1 on google for "switch in t-sql".


    I don't think the OP is asking for a case statement, which can only be used inside a select or update statement.  I think he truly wants a switch statement would break down into if/else logic.  I don't think t-sql has this.  You need to use If statements.

    Tuesday, September 14, 2010 6:24 PM
  • Here's an example that might help:

    declare @var as int
    declare    @EvenNumbers as table(
        EvenNum int
    declare    @OddNumbers as table(
        OddNum int

    set @var =4

    if(@var % 2 = 0) begin
        insert into @EvenNumbers(EvenNum) values (@var)
        print 'It''s even!'

    if(@var % 2 = 1) begin
        insert into @OddNumbers(OddNum) values(@var)
        print 'it''s odd!'

    select    *
    from    @EvenNumbers
    select    *
    from    @OddNumbers

    "Winners never quit, and quitters never win."
    • Proposed as answer by DBDuck Sunday, May 22, 2011 1:00 AM
    Sunday, May 22, 2011 12:59 AM
  • Posting my solution as this is the first article that comes up on the search and I find using CASE statement on its own isn't possible and must be used within a SELECT.

    My recommendation is below to simulate switch in TSQL with IF, ELSE IF, ELSE IF, ..., ELSE.

    IF (@express = 1)


         SELECT 'Stmt 1';

    ELSE IF (@expression = 2)


         SELECT  'stmt 2'




         SELECT 'undefined/invalid expression'


    I hope this helps people out as SWITCH is even available in POWERSHELL and that is newer than T-SQL. MS please add SWITCH to T-SQL ;)

    Good day,


    Monday, November 23, 2015 3:45 PM