SQL Server – How to Use CASE Expression in SQL Server???

Hey Folks, you might have heard and used CASE in other languages. But SQL CASE expression is different. SQL Server’s CASE expression is a flexible and excellent means of building dynamic expressions. It is not used for programmatic flow of control, but rather to logically determine the value of an expression based on a condition.

Using a CASE expression to handle the various calculations and executing the entire operation in a single query enables SQL Server to optimize the process and make it dramatically faster.

Since the case expression returns only the expression, so it may be used anywhere in the SQL DML statements like SELECT, INSERT, UPDATE, or DELETE, where an expression may be used. A case expression can even be used mid-expression to create a dynamic formula – very powerful.

The CASE statement has two formats, simple and searched:

Simple Case:                                       

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The variable is presented first and then each test condition is listed. But it has limitation to perform only equal comparisons.
• The CASE expression sequentially checks the WHEN conditions and returns the THEN value of the first true WHEN condition.
• The syntax for the Simple CASE expression is:

CASE input_expression 
WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

• For e.g., we are using the CASE expression for the table Students2 created in the Test database, which determines the Students Result:

Use TEST
SELECT Name, 
CASE SID
WHEN 1 THEN 'Pass'
WHEN 2 THEN 'Fail'
ELSE 'Supplementary'
END AS Result
FROM dbo.Students2

You can also see the image of the result set:

• The CASE expression concludes with an end and an alias ‘Result’ and the CASE expression evaluates the ‘SID’ column.

Boolean Case (Searched Case):

• The Searched CASE expression evaluates a set of Boolean expressions to determine the result.
• The Boolean form of case is more flexible than the simple form in that each individual case has its own Boolean expressions.
• So, not only each WHEN condition can include comparisons other than ‘=’, but the comparisons can also reference to different columns.
• The syntax for the Searched CASE expression is:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
  END

• For e.g., now we are using Boolean CASE for knowing that is there the Reality Still exists, its Piyush Birthday ,or it’s a Normal Life:

SELECT
CASE
WHEN 1<0 THEN 'There is no Reality.'
WHEN CURRENT_TIMESTAMP= '2011-07-06 00:01:00.001'
THEN 'Piyush Birthday.'
WHEN 1>0 THEN 'Normal Life.'
END AS RealityCheck;

You can also see the image of the result:

• As you have seen here, the Searched CASE expression offers more flexibility than the simple CASE.
• The Boolean CASE expression can also include Boolean operators.

Well this is all about CASE expressions, hope you like it 🙂

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook