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:

1_How_to_Use_CASE_Expression_in_SQL_Server

  • 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:

2_How_to_Use_CASE_Expression_in_SQL_Server

  • 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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

5 Comments on “SQL Server – How to Use CASE Expression in SQL Server???”

  1. Please help me in this query

    SELECT ID,voucherDate,accountID,Remarks,

    CASE WHEN transactionType=’Credit’ THEN Amount as Credit

    ELSE Amount as Debit

    FROM JournalEntries

    WHERE accountID = @accountId and reconciled=null

Leave a Reply

Your email address will not be published. Required fields are marked *