Where clause in stored procedure

RIAS
RIAS used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi
Please consider something similar to the following:

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

Open in new window

IT Engineer
Distinguished Expert 2017
Commented:
Only need to perform a single test:
WHERE ISNULL([TravelDate],'') = ISNULL(@TravelDate,'')

Open in new window

Author

Commented:
Cheers!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

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

Open in new window

?

Cheers
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't think so.
How long took the original solution and how long it takes after you used mine suggestion?

Author

Commented:
Can't notice the difference,
Is it worth changing the original code then as I will have to modify nearly 100 Stored Procedures?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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 ;)

Author

Commented:
Cheers!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial