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

SQL Server - CASE in WHERE Clause

Article:
Viewed:  706 
Posted On:  3/25/2016 6:39:48 AM 
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.

1.png

Image 1.

Data in my this table:

2.png

Image 2.

Stored procedure for this requirement:

3.png

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:

 

4.png

 

Image 4.

 

5.png

 

Image 5.

 

6.png

 

Image 6.

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