January 27, 2008 at 11:45 am
I have the following two SQL statements in a SP:
IF (@param1 IS NULL)
SELECT * FROM Table WHERE ID = @param2 AND QTY = @param3
ELSE
SELECT * FROM Table WHERE ID = @param2 AND QTY = @param4
Is there a way to have just a single SQL statement using IF or CASE at the WHERE clause? For example:
SELECT * FROM Table WHERE ID = @param2 AND QTY = (CASE WHEN @param1 IS NULL THEN @param3 WHEN @param1 IS NOT NULL ELSE @param4 END).
I know this will not work.
Thanks for any suggestions.
sg2000
January 27, 2008 at 12:15 pm
Close
SELECT * FROM tbl WHERE ID = @param2 AND QTY = (CASE WHEN @param1 IS NULL THEN @param3 ELSE @param4 END)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2008 at 6:54 pm
You can use Gail's suggestion, but building queries like this is inefficient and can cause you performance issues with large data sets.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2008 at 8:12 pm
Thank you very much, Gail for the suggestion.
Steve, if that Gail's suggested is costly, do you recommend that we use two similar SQL statements?
Thanks all,
sg2000
January 28, 2008 at 1:07 am
Steve Jones - Editor (1/27/2008)
You can use Gail's suggestion, but building queries like this is inefficient and can cause you performance issues with large data sets.
I was thinking the same thing. This particular construct shouldn't be too bad, because the case is just on parameters and not the columns. Should allow effective index usage (note: should. I haven't tested) Might give some parameter sniffing issues though
The 'normal' construct of
WHERE (@param1 is null or col1 = @param1) AND (@param2 is null or col2 = @param2) ... or similar are very bad, as the optimiser often misjudges the constant evaluations.
I wouldn't suggest 2 sql statements within an if statement either. When a proc is compiled, all the queries in the proc are compiled and optimised, based on the values of the parameters for that run. If you've got IF statements based on param values that lead to 2 or more very different queries, you can get very erratic performance (The optimiser optimised for one particular set of param values, but that plan will be reused for all values)
Normally for multiple possible where clauses, especially complex ones, I'll suggest either multiple procs, one for each possible query, dynamic SQL (which has its own downsides) or marking the search proc with RECOMPILE to ensure an optimal plan for each run.
Does that make any sense?
I'd suggest you test carefully. If the performance of your query is good, then leave it. If not, then consider other options.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 1:17 am
You could still combine them into a single query using a UNION and a where clause that checks for the parameter being null or not null as appropriate.
You'll want to try the various approaches and see what the query plans are like. Definitely try to avoid table scans if possible. Because the query's conditions are changing based on parameters I'd expect the query processor to be able to smartly evaluate the formulae once and avoid costly row-by-row processing. However you should check that this is indeed the case.
January 28, 2008 at 4:32 am
I go with Ian's suggestion. I always try various combinations then run through a Query Execution plan to see which one works best for my current needs. I have found that sometimes CASE is worse than IF/ELSE and othertimes the reverse is true. A UNION might solve the problem, I've certainly used that option also. But it just depends on what else you're trying to do.
Test the options out thoroughly before finalizing the code and you'll get the best possible query for your DB and circumstances.
January 28, 2008 at 5:42 am
We've actually got a method that does this pretty well. We try to use it sparingly, but some of our reports from some of our systems have legitimate requirements to pass in a variable set of parameters. An MS consultant introduced this to us:
SELECT...
FROM...
WHERE...
AND CASE WHEN @MyParam IS NULL THEN 1
WHEN @MyParam = a.CbiTypeId THEN 1
ELSE 0
END = 1
It looks funky, but it will use indexes appropriately when the column is referenced. I will add the old caveat emptor.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2008 at 5:45 am
Grant,
What does the "End = 1" do? I understand the "END" keyword. I'm just trying to figure out the rest of it.
January 28, 2008 at 6:06 am
Basically the case statement is resolving either to 1 or 0. If it's 1, then 1=1 at the end of the case statement. If it's zero, then 0 certainly does not equal 1. So for NULL values on the parameters, it sets to 1, and 1=1 and the query moves on. If the column value equals the parameter, then it returns 1. Otherwise, it returns 0. It's a very odd bit of code. The amazing thing to me is that it doesn't perform this in a RBAR manner.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2008 at 6:11 am
Intereeestiinnngg.
I'll have to keep this one in mind. Thanks, Grant!
January 28, 2008 at 6:40 am
You can dynamically prepare a SQL statement in a VARCHAR variable and then execute it using EXEC statement as follows..
DECLARE @query VARCHAR(1000)
SET @query ='SELECT * FROM myTable
WHERE Id=''' + @param2 + ''''
IF @param1 IS NULL
BEGIN
SET @query =@query + ' AND qty = ''' + @param3 + ''''
END
ELSE
BEGIN
SET @query =@query + ' AND qty = ''' + @param4 + ''''
END
PRINT @qurey
EXEC (@qurey)
P.S.If @param2,@ param3 and @param4 are not VARCHAR then you need to convert them to VARCHAR using CONVERT function.
like this...
SET @query =@query + ' AND qty = ''' + CONVERT(VARCHAR,@param3 )+ ''''
..Hope this will help you.
January 28, 2008 at 7:46 am
The problem with dynamic queries is that they can lead to recompiles and poor plan reuse. While they solve some problems, they can introduce others.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2008 at 7:52 am
And they have security implications. Dynamic SQL has its place, but it has a number of downsides.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply