How do I do multiple CASE WHEN conditions using SQL Server 2008?

sql, sql-server-2008

Solution

There are three formats of case expression. You can do `CASE` with many `WHEN` as;

CASE  WHEN Col1 = 1 OR Col3 = 1  THEN 1 
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

Or a Simple `CASE` expression

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END

Or `CASE` within `CASE` as;

CASE  WHEN Col1 < 2 THEN  
                    CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

Problem

What I'm trying to do is use more than one CASE WHEN condition for the same column. Here is my code for the query: ``` SELECT Url='', p.ArtNo, p.[Description], p.Specification, CASE WHEN 1 = 1 or 1 = 1 THEN 1 ELSE 0 END as Qty, p.NetPrice, [Status] = 0 FROM Product p (NOLOCK) ``` However, what I want to do is use more then one WHEN for the same column "qty". As in the following code: ``` IF // CODE ELSE IF // CODE ELSE IF // CODE ELSE // CODE ```

Original source