SQL Server: Use CASE in WHERE Clause

In this article I am going to show how we can use CASE in WHERE clause in SQL Server

My requirement is to fetch records from below tables on the base of supplied parameter but  the business requirement is if I pass null value then it should return all records and if I pass any parameter value then it should return matched records.

table

Data in my table:


data

Stored procedure for this requirement:


requirement
  1. ALTER PROCEDURE [dbo].[SearchEmployee]  
  2. (  
  3. @NAME VARCHAR(50)='',  
  4. @City VARCHAR(50)='',  
  5. @Joining_Date VARCHAR(50)=''  
  6. )  
  7. AS  
  8. SELECT*FROM Emp_Information where  
  9. NAME=case @NAME when''then NAME Else @NAME end  
  10. ANDCONVERT(DATE,CONVERT(VARCHAR(10),Joining_Date, 101))=case @Joining_Date when''thenCONVERT(DATE,CONVERT(VARCHAR(10),Joining_Date, 101))Else @Joining_Date end  
  11. AND City=case @City when''then City Else @City end  
Now execute Stored Procedure,

Execute

Execute

Execute

Read more articles on SQL Server: