Using the CASE Statement in SQL Server

  • Checks if the condition specified by WHEN is met
  • Returns the result specified by THEN
  • At least one WHEN… THEN expression is obligatory
  • The END keyword must finalize the CASE statement
  • The ELSE argument is optional — CASE will return the result specified by it if no conditions are TRUE
  • No more than 10 levels of nesting for SQL Server are allowed
  • Simple CASE statement defines the result value by evaluating an expression against multiple values.
  • Searched CASE statement defines the result value by evaluating a set of Boolean expressions.

SQL CASE statement with multiple conditions

We can add multiple conditions to the CASE statement and combine them with the AND operator:

CASE with the ORDER BY clause

We can apply CASE to change the output order and instruct how to sort the results according to the requirements.

CASE with the GROUP BY clause

The CASE expression allows for arranging the query output in the required way:

CASE with INSERT and UPDATE

We can use CASE in the basic INSERT and UPDATE operations to make them more precise.

Using CASE WHEN with SUM()

The CASE WHEN expression with the SUM() function will assign values by specific conditions and sum them correctly.

Conclusion

The CASE statement is helpful for many operations in SQL Server. It makes queries more versatile and helps bring more precise results. It is worth applying SQL CASE in your work.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jordan Sanders

Jordan Sanders

PR Manager at Devart since 2010 | Obsessed with the promotion of database development optimization