In this article I am going to show how we can use CASE
in WHERE clause:
My requirement: I have to fetch records from below
tables on the base of supplied parameter but 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.
Image 1.
Data in my this table:
Image 2.
Stored procedure for this requirement:
Image 3.
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
AND CONVERT (DATE, CONVERT(VARCHAR(10),Joining_Date, 101)) = case
@Joining_Date
when
'' then CONVERT (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:
Image 4.
Image 5.
Image 6.