Where clause in stored procedure

Hello,

 Ihave a where clause in SP ,is a better way of writing this :
WHERE(
					[TravelDate] = @TravelDate OR 
					([TravelDate] is null AND @TravelDate is null) OR
					([TravelDate] is null AND @TravelDate ='') OR
					([TravelDate] ='' AND @TravelDate is null))

Open in new window


Cheers
RIASAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Only need to perform a single test:
WHERE ISNULL([TravelDate],'') = ISNULL(@TravelDate,'')

Open in new window

1
 
Barry CunneyConnect With a Mentor Commented:
Hi
Please consider something similar to the following:

WHERE(
					[TravelDate] = @TravelDate OR
					(ISNULL([TravelDate],'') = '' AND ISNULL(@TravelDate,''))
) 

Open in new window

0
 
RIASAuthor Commented:
Cheers!
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
RIASAuthor Commented:
Will there be performance difference if I modify the Stored Procedure with

WHERE ISNULL([TravelDate],'') = ISNULL(@TravelDate,'')

Open in new window

?

Cheers
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think so.
How long took the original solution and how long it takes after you used mine suggestion?
0
 
RIASAuthor Commented:
Can't notice the difference,
Is it worth changing the original code then as I will have to modify nearly 100 Stored Procedures?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I guess TravelDate is not indexed, right?
IMO doesn't worth the work. It's mostly code reduction than performance gain.
Take it as code tuning lesson ;)
1
 
RIASAuthor Commented:
Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.