Use the CASE Statement in a SQL SELECT Clause

Use the CASE Statement in a SQL SELECT Clause

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 WHEN clause:

DECLARE @TestVal intSET @TestVal = 3SELECT	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 intSET @TestVal = 5SELECT	CASE		WHEN @TestVal <=3 THEN 'Top 3'		ELSE 'Other'	END
See also  Why ChatGPT Is So Important Today

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist