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

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
     CASE
          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

Answer 1

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