Make condition optional in WHERE clause

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hello,

I am passing five parameters to a stored procedure, among which 2 are required and all other are optional.

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
) 
AS 
	
BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN 
		CONVERT(DATETIME, @StartDate+'00:00:00',120) and 
		CONVERT(DATETIME,@EndDate+'23:59:59',120)
		
END

My code is not working if I pass @RequestStatusId as null.
I also want it to be optinal when @RequestStatusId is zero

Thanks

Posted 1-Jan-12 19:14pm

tejashri.gandhi

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

tejashri.gandhi · Accepted Answer · 2012-01-01T19:21:00

Solution 1

EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL or @RequestStatusId=0 THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN 
		CONVERT(DATETIME, @StartDate+'00:00:00',120) and 
		CONVERT(DATETIME,@EndDate+'23:59:59',120)

Posted 1-Jan-12 19:21pm

tejashri.gandhi

Comments

Amir Mahfoozi 2-Jan-12 10:19am

+5

RDBurmon · Accepted Answer · 2012-01-02T00:36:00

Solution 3

Well , You haven't specified the Required parameters so I am considering first two parameters as mandatory parameters

So you SP will be as below

SQL

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
) 
AS 
	
BEGIN  

	 --IF @RequestStatusId = 0 THEN @RequestStatusId=NULL 
         IF @RequestStatusId = 0 SET @RequestStatusId=NULL 
	 SELECT @StartDate = @StartDate + '00:00:00'	
	 SELECT @EndDate=@EndDate + '23:59:59'

	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=COALESCE(@RequestStatusId,RequestStatusId)
		AND AddedOn	BETWEEN COALESCE(@StartDate,AddedOn) AND  COALESCE(@EndDate,AddedOn)
		
		
END

Posted 2-Jan-12 0:36am

RDBurmon

Updated 2-Jan-12 2:18am

v5

Comments

tejashri.gandhi 2-Jan-12 7:20am

Thanks Rahul.. I got to learn something new from this..

tejashri.gandhi 2-Jan-12 7:24am

But there is one problem.. If I pass "RequestStatusId=0", it is not working..

RDBurmon 2-Jan-12 7:30am

Please see the updated ans.

tejashri.gandhi 2-Jan-12 7:51am

I think int value can not be null.. When I am trying this conversion i get following error..
Incorrect syntax near '@RequestStatusId'.

RDBurmon 2-Jan-12 8:19am

Sorry I Forgot to add "SET " please try to use it now

tejashri.gandhi 2-Jan-12 23:03pm

Hey...thanks Rahul...I am getting it now..Thank you so much :)

RDBurmon 3-Jan-12 1:15am

No Problem :)

[no name] 4-Jan-12 21:10pm

Worth noting is that the coalesce on AddedOn has changed the purpose of the query.

In the original post if @StartDate and @EndDate were null then no rows would be returned from ...

AND AddedOn BETWEEN
CONVERT(DATETIME, @StartDate+'00:00:00',120) and
CONVERT(DATETIME,@EndDate+'23:59:59',120)...

now with ...

AND AddedOn BETWEEN COALESCE(@StartDate,AddedOn) AND COALESCE(@EndDate,AddedOn)

all rows are returned :-)

Sarvesh Kumar Gupta · Accepted Answer · 2012-01-01T19:27:00

Solution 2

Use below code

CASE WHEN isnull(@RequestStatusId,0)=0 THEN

Change your query with this. it solves your problem.

Posted 1-Jan-12 19:27pm

Sarvesh Kumar Gupta

Comments

RDBurmon 3-Jan-12 3:11am

I think COALESCE(@RequestStatusId,0) is very clean way to achieve this. Do you think the same as well ?

[no name] 4-Jan-12 21:18pm

or even...

and RequestStatusId=isnull(nullif(@RequestStatusId,0),RequestStatusId)

:-)

Make condition optional in WHERE clause

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Make condition optional in WHERE clause

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us