Simple Example On SQL CASE Expression

Introduction

If you are working with SQL SERVER then you are definitely going to need this article. Let me explain a bit briefly. 

You have one table named [User] where, you are storing 3 type of user's data such as

• Clients data
• Employee data
• Admin data

Consider the case if you are asked to write a stored procedure that needs to fetch the respective user data. What will you do ? I can say most of the people will go for a If-Else condition to fetch the data.

But the Case expression will solve your problem in just simple way. Find it below....

   

Behind The Scene

First see if you want to fetch the client data, employee data and admin data in If-Else condition.

-- AUTHOR: Tapan kumar
-- CREATE DATE: --                                                  
-- PROJECT:          
-- CREATED BY: Tapan kumar
-- MODIFIED BY:   
-- MODIFICATION DATE: 28DEC2013  
-- Last MODIFICATIONS :                                                
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type. 
-- UNIT TESTING :  [Getuseranddetailstosendexpirynotification] 
-------------------------------------------------------
CREATE PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
  @FilterType INT
AS
  BEGIN
      -- @FilterType = 1 (will select clients only)
      -- @FilterType = 2 (will select employees only)
      -- @FilterType = 3 (will select admins only)
      
      SET nocount ON;

      IF @FilterType = 1 -- For selecting client data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isclient = 1
                   AND .isdeleted = 0
        END
      ELSE IF @FilterType = 2 -- For selecting employee data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isemployee = 1
                   AND .isdeleted = 0
        END
      ELSE IF @FilterType = 3 -- For selecting admin data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isadmin = 1
                   AND .isdeleted = 0
        END
  END 

This is how your stored procedure will look like. You need to write the select query 3 times.

But here comes the use of CASE expression to save your time. See the below stored procedure to do the same thing..

-- AUTHOR: Tapan kumar
-- CREATE DATE: --                                                  
-- PROJECT:          
-- CREATED BY: Tapan kumar
-- MODIFIED BY:   
-- MODIFICATION DATE: 28DEC2013  
-- Last MODIFICATIONS :                                                
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type. 
-- UNIT TESTING :  [Getuseranddetailstosendexpirynotification] 
-----------------------------------------------
ALTER PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
  @FilterType INT
AS
  BEGIN
      -- @FilterType = 1 (will select clients only)
      -- @FilterType = 2 (will select employees only)
      -- @FilterType = 3 (will select admins only)
      
      SET nocount ON;

      SELECT id,
             firstname,
             lastname,
             salary
      FROM   [user]
      WHERE  1 = CASE
                   WHEN @FilterType = 1
                        AND isclient = 1
                        AND .isdeleted = 0 THEN 1
                   WHEN @FilterType = 2
                        AND isemployee = 1
                        AND .isdeleted = 0 THEN 1
                   WHEN @FilterType = 3
                        AND isadmin = 1
                        AND .isdeleted = 0 THEN 1
                   ELSE 0
                 END
  END 

In the above procedure you can see how I have written a single select query that behave differently depending on the @filterType .

Here is how it behaves

• @filterType = 1, it will select the client data. 
• @filterType = 2, it will select the employee data. 
• @filterType = 3, it will select the admin data.

Hope this will be helpful to you.

Happy Coding...