May 12, 2014 at 9:06 am
Hi,
I have a situation where if a parameter passed in by the user is equal to 'No Group' I want to perform one where clause and if it isn't, perform a different where clause.
something like
WHERE
<userParam> = 'No Group'
(pg.ProGroup IS NULL)
<userParam> != 'No Group'
pg.ProGroup IN (userParam)
When I combine the two clauses into one it doesn't work. If say userParam = Sundries, then the below clause brings back Sundries and No Group
(pg.ProGroup IS NULL OR (pg.ProGroup IN (userParam))
If userParam = 'No Group' then it does just bring back 'No Group' as expected tho.
So I was wondering if there is some conditional statements or similar I can use in the WHERE clause.
thanks,
May 12, 2014 at 9:31 am
Something like this should work, although performance could suffer since any index on ProGroup will not be used:
WHERE COALESCE(pg.ProGroup,'No Group') = @userParam
John
May 12, 2014 at 9:45 am
You're a genius John (or at least someone who knows more sql than me 🙂 thanks.
May 12, 2014 at 9:54 am
Of course, the function on that column will preclude index seeks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2014 at 9:57 am
Grant Fritchey (5/12/2014)
Of course, the function on that column will preclude index seeks.
Indeed. Hence the warning about performance.
John
May 12, 2014 at 10:00 am
John Mitchell-245523 (5/12/2014)
Indeed. Hence the warning about performance.John
I do occasionally have reading problems. And this was one of them. Apologies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2014 at 10:01 am
Here is another way.
where (@userParam = 'No Group' and pg.ProGroup IS NULL)
OR
pg.ProGroup = @userParam
The problem with this approach is the query plan may need to be different depending on the value of @userParam. This becomes a type of catch all query. Check out Gail's article which explains the performance challenges and several ways to combat them. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2014 at 10:07 am
The performance seems fine, I'll test it with our biggest client tomorrow but I'll note your comments/links anyway for future ref, thanks.
May 12, 2014 at 10:29 am
mattech06 (5/12/2014)
The performance seems fine, I'll test it with our biggest client tomorrow but I'll note your comments/links anyway for future ref, thanks.
Make certain you read that article. It is very easy to deal with the issue. It also explains why doing a typical performance evaluation can be quite misleading to the actual performance in the field. You need to test both passing in a value and the 'No Group' value. Go back and forth between those a few times. The problem is that performance will be fine for one of the options but can likely be horrible for the other one because the plan is cached.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply