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.



Data in my table:



Stored procedure for this requirement:

ALTER PROCEDURE [dbo].[SearchEmployee]
(
@NAME VARCHAR(50)='',
@City VARCHAR(50)='',
@Joining_Date VARCHAR(50)=''
)
AS
SELECT*FROM Emp_Information where
NAME=case @NAME when''then NAME Else @NAME end
ANDCONVERT(DATE,CONVERT(VARCHAR(10),Joining_Date, 101))=case @Joining_Date when''thenCONVERT(DATE,CONVERT(VARCHAR(10),Joining_Date, 101))Else @Joining_Date end
AND City=case @City when''then City Else @City end

Now execute Stored Procedure,

Read more articles on SQL Server:

• Introduction to CDC of SQL Server - Part Three
• Stored Procedure With Common Table Expression Or CTE