Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - CASE - Conditional Expressions

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Is a Boolean Value?
  2. What Are Conditional Expressions?
  3. What Are Comparison Operations?
  4. How To Perform Comparison on Exact Numbers?
  5. How To Perform Comparison on Floating Point Numbers?
  6. How To Perform Comparison on Date and Time Values?
  7. How To Perform Comparison on Character Strings?
  8. What To Test Value Ranges with the BETWEEN Operator?
  9. What To Test Value Lists with the IN Operator?
  10. What To Perform Pattern Match with the LIKE Operator?
  11. How To Use Wildcard Characters in LIKE Operations?
  12. How To Test Subquery Results with the EXISTS Operator?
  13. How To Test Values Returned by a Subquery with the IN Operator?
  14. What Are Logical/Boolean Operations?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...