Email: Password:       Forgot Password 
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.

SQL Server - CASE in WHERE Clause

Viewed:  2028 
Posted On:  25/03/2016 06:39:48 
How we can use CASE in WHERE Clause in a SQL Server Statement? 


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)=''
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.

         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us