Tow Ways to Use SQL -|- CASE-WHEN-THEN-ELSE-END RRS feed

  • Question

  • User-589637085 posted

    I always get confused with the syntax of CASE in T-SQL. Basically there are two ways to use CASE. So I decided to post it for reference

    Suppose "TermID" is the field in the Database Table

    1)The first syntax is more like other languages where we pass some value in SWITCH and then chenk in CASE

    SELECT InvoiceNumber, TermID, 
    CASE TermID
    WHEN 1 THEN 'I am One'
    WHEN 2 THEN 'I am Two'
    WHEN 3 THEN 'I am Three'
    ELSE 'Undefined'
    END AS TermName
    FROM Invoices

    2) Second syntax is more kind of IF/ELSE (I am not passign anything to CASE, thats the basic difference. Also 'TermID < 5' can be raplaced with any expression). Thats why I consider this format more like IF/ELSE structure

    SELECT InvoiceNumber, TermID
    WHEN TermID < 5 THEN 'I am less then five''
    WHEN TermID = 5 THEN 'I am equal to five'
    ELSE 'I am greater than five'
    END AS TermName
    FROM Invoices

    Hope this helps someone

    Friday, July 16, 2010 3:38 PM

All replies

  • User-1465674731 posted

    hey if you want to do only equality check then first one you will  use (you can also use second one)

    you can use anyone of them

    Saturday, July 17, 2010 5:34 AM