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: