• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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
0
RIAS
Asked:
RIAS
  • 4
  • 3
2 Solutions
 
Barry CunneyCommented:
Hi
Please consider something similar to the following:

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

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only need to perform a single test:
WHERE ISNULL([TravelDate],'') = ISNULL(@TravelDate,'')

Open in new window

1
 
RIASAuthor Commented:
Cheers!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now