Small WHERE clause problem
-
Goodmorning everybody,
I have a simple stored procedure which returns invoice details A) for invoices within a given period or B) for all invoices. Something like
ALTER procedure _getInvoices ( @DateFrom as datetime = null, @DateUntil as datetime) AS
SELECT invoice.* FROM invoice WHERE (@DateFrom IS NULL AND @DateUntil IS NULL) or (invoice.date BETWEEN @DateFrom AND @DateUntil)
As usual this example is simplified, but the question is: is there a better solution in terms of performance?
Thank you for your attention,
Gerry
Dutch Anti-RBAR League -
Avoid NULL !
ALTER procedure _getInvoices ( @DateFrom as datetime = null, @DateUntil as datetime) AS
if @DateFrom IS NULL
begin
set @DateFrom = '1900-01-01 00:00:00.000'
end
if @DateUntil IS NULL
begin
set @DateUntil = '2100-01-01 00:00:00.000'
end
SELECT *
FROM invoice
WHERE [date] BETWEEN @DateFrom AND @DateUntil
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes, you're right about NULL. I just tried the use of a boolean bitUseDateSelection (which defaults to 0 unless both dates aren't NULL).
The result would be something like: SELECT * FROM invoice
WHERE @bitUseDateSelection = 0 OR [date] BETWEEN @DateFrom AND @DateUntil
Thanks for your reply.
Gerry
Dutch Anti-RBAR League -
That is not what I mean.
In my previous reply I altered the sp so it would use only known values !
What you want to perform -IMO- is to have predicates activated using whatever mechanism. I don't know if that's the good way.
Anyway perform positive testing , combined with AND.
WHERE @bitUseDateSelection = 1 and [date] BETWEEN @DateFrom AND @DateUntil
Prefer the use of "and".
I try to write optimal queries for each combination or grouped combination (when each is to many) of search-arguments, so the querypath is optimal.
Check your querypaths and determine the combinations.
Your effort to optimize will be a gain for every appliciaton using your sp.
Keep in mind that your sp will only be compiled once unless you specify it has to recompile (check BOL)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Roger that
Thanks.
Gerry
Dutch Anti-RBAR League
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply