How to have multiple WHERE clauses depending on condition

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You can use Gail's suggestion, but building queries like this is inefficient and can cause you performance issues with large data sets.

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant,

    What does the "End = 1" do? I understand the "END" keyword. I'm just trying to figure out the rest of it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Intereeestiinnngg.

    I'll have to keep this one in mind. Thanks, Grant!

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply