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

    Question

  • 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 05, 2007 4:04 AM

Answers

All replies

  • Hi,

    See the following example

     

    DECLARE @TestVal int
    SET @TestVal = 3

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

    Thursday, April 05, 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 01, 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 ,
    Amount,
    getdate()
    From myTableA
    Where Year = 2010
    and Period = 5

    Or

    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 01, 2010 9:43 PM
    Thursday, July 01, 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!'
    end

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

    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