SQL Server FAQ - CASE - Conditional Expressions

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

What Are Conditional Expressions?

A conditional expression returns one of the given expressions based a specific condition. SQL Server 2005 offers the CASE operator to present a conditional expression with two syntaxes:

1. CASE with simple conditions
CASE test_value 
   WHEN value_1 THEN expression_1
   WHEN value_2 THEN expression_2
   ...
   WHEN value_n THEN expression_n
   ELSE expression_o
   END
-- Returns "expression_x" 
   if "test_value" equals to "value_x". 

2. CASE with complex conditions
CASE
   WHEN condition_1 THEN expression_1
   WHEN condition_2 THEN expression_2
   ...
   WHEN condition_n THEN expression_n
   ELSE expression_o
   END
-- Returns "expression_x" 
   if "condition_x" is TRUE.

Here are two examples on how to use the CASE operator:

DECLARE @command VARCHAR(10);
SET @command = 'S';
SELECT CASE @command 
   WHEN 'A' THEN 'Add'
   WHEN 'S' THEN 'Save'
   WHEN 'Q' THEN 'Quit'
   ELSE 'Unknown command.'
   END;
GO
Save

DECLARE @command VARCHAR(10);
SET @command = 'Q';
SELECT CASE  
   WHEN @command = 'A' THEN 'Add'
   WHEN @command = 'S' THEN 'Save'
   WHEN @command = 'Q' THEN 'Quit'
   ELSE 'Unknown command.'
   END;
GO
Quit

(Continued on next topic...)