SQL Server provides a mechanism for returning different values in a SELECT clause based on Boolean conditions: the CASE statement. This statement resembles Visual Basics Select Case statement.
The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator. The syntax is:
CASE [expression] WHEN [value | Boolean expression] THEN [return value] [ELSE [return value]] END The [expression] is optional and contains a table column or a variable. When you specify [expression] directly after the CASE, you must populate the [value] parameter in the WHENclause:
DECLARE @TestVal int SET @TestVal = 3 SELECT CASE @TestVal WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' ELSE 'Other' END SQL Server compares this value to the expression and when the values match, it returns the THEN clauses [return value]. If none of the WHEN clauses equates to true, SQL Server returns the [return value] in the optional ELSE clause. If the ELSE clause is omitted and no value is matched, NULL is returned.If you dont specify [expression], you must include the [Boolean expression] in the WHEN clause. This can contain any valid Boolean expression SQL Server allows:
DECLARE @TestVal int SET @TestVal = 5 SELECT CASE WHEN @TestVal <=3 THEN 'Top 3' ELSE 'Other' ENDSOURCE | LINK | LANGUAGE | ENGLISH |