Wierd Issue
-
Hi,
Is there any way to get all records from a Table if 'Null' is passed as Parameter in a stored procedure and filter records on value if something else is passed?? Generating and executing queries dynamically is not an option for me. Also, I have 3 parameters and writing 'if' or 'select' statements will require me to write 8 different queries.
dO I have any other option ?? Can it be done in a single query ??
Regards,
Hatim Ali.
-
Hi Hatim!
I belive you are looking for a sulotion published just a couple of days ago:
http://qa.sqlservercentral.com/columnists/SFilippidis/conditionalstatementsinwhereclauses.asp
Good luck!
robbac
___the truth is out there___ -
where col1 = isnull(@p1,col1)
and col2 = isnull(@p2,col2)
and col3 = isnull(@p3,col3)
But beware this will invoke a table or index scan.
Far away is close at hand in the images of elsewhere.
Anon. -
maybe for performance reasons it is better to split up your queries !
This effort must only be done once (and at maintenance time copy-paste) but will payback during its usage !
Compare execution plans !
I've done this kind of operation a couple of times, splitting one "general" query into 41 tuned ones (based on combinations of inputparameters).
Performance increased astonishingly and my spent time was payed back within a day because these sp's where used very often !
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
-
The ISNULL solution is not bad, but, as the author indicated, it can result in full table scans. You can probably avoid that by using an OR clause that will short circuit the comparison when the parameter is null.
where (@p1 is null or col1 = @p1)
and (@p2 is null or col2 = @p2)
and (@p3 is null or col3 = @p3)
-
Thanks Peter... It worked
-
Did you actually check execution plans for each method?
I'm just curious. I would bet that the OR gives better performance, but it depends on the data itself and indexing.
-
No not yet.... But i'll do that and post results shortly....
Thanks anyway ...
-
Yes I would be interested as well. The OR method is the way I do it, the ISNULL I got from other threads on this site and seemed a simpler neater way of achieving the same.
BTW, the OR method will still do an Index Scan not an Index Seek
Far away is close at hand in the images of elsewhere.
Anon. -
Hi
There is much debate on optional Optional parameters we have a VLDB with over 50000000 records. Based on the condition shown above we have found the following optimisations:
Worst way:where optional param is at the end of the or (col1 = @p1 or @p1 is null ), if the optional parameter is NULL then this typically causes col1 = NULL to be evaluated and may cause table scans to occur.
Better way: where optional param is at the beginning relying on MS-SQL short-circuiting the expression (e.g. @p1 is null or col1 = @p1).
Much better way: use case statements as this forces short-circuiting to occur, making sure that the condition with the least amount of processing to occur first (e.g case when @p1 is null then 1 when col1 = @p1 then 1 else 0 end = 1.). If the optional parameter is NULL then the second WHEN statement is never processed.Hope this helps
rgrds
James
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply