none
Condition WHERE statements in SELECT?

    Domanda

  • Let's say I have a stored procedure that receives an optional parameter @merchantID. The stored procedure looks like this:

    SELECT sum(total) from MERCHANTS GROUP BY MERCHANT_ID

    Without using dynamic sql, would it be possible to condition the WHERE statements that could be added to the SELECT? For example, if @merchantID is null then the SELECT would remain the same as above.

    But if @merchantID has a value, then the WHERE would be added to the SELECT:

    SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = @merchantID --Because @merchantID is not empty
    GROUP BY MERCHANT_ID

    Thanks.


    • Modificato rbhatup lunedì 12 marzo 2012 12:40
    lunedì 12 marzo 2012 12:39

Risposte

Tutte le risposte

  • SELECT sum(total) from MERCHANTS
    WHERE MERCHANT_ID = @merchantID OR @merchantID is null
    GROUP BY MERCHANT_ID


    • Modificato tkrasinger lunedì 12 marzo 2012 12:40
    lunedì 12 marzo 2012 12:40
  • SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = @merchantID --Because @merchantID is not empty
    GROUP BY MERCHANT_ID

    SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = (CASE WHEN @merchantID IS NULL THEN MERCHANT_ID ELSE @merchantID END )

    GROUP BY MERCHANT_ID


    Murali Krishnan


    • Modificato Murali_CHN lunedì 12 marzo 2012 12:47 a
    lunedì 12 marzo 2012 12:46
  • One more

    SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = COALESCE(@merchantID,MERCHANT_ID )
    GROUP BY MERCHANT_ID


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    lunedì 12 marzo 2012 12:52
    Postatore
  • Yes, as it was shown. Note it may hamper perfomance, optimizer most probably will ignore index on MERCHANT_ID.

    I'd prefer  plain

    if (@merchantID is not null)

    SELECT sum(total) from MERCHANTS
    WHERE MERCHANT_ID = @merchantID

    GROUP BY MERCHANT_ID

    else

    SELECT sum(total) from MERCHANTS
    GROUP BY MERCHANT_ID

    Or SP_EXECUTESQL dynamic sql 


    Serg

    • Proposto come risposta Naomi NModerator lunedì 12 marzo 2012 13:21
    • Contrassegnato come risposta KJian_ lunedì 19 marzo 2012 06:32
    lunedì 12 marzo 2012 13:11
  • SELECT SUM(total) from MERCHANTS 
    WHERE	MERCHANT_ID = ISNULL(@merchantID,MERCHANT_ID)
    GROUP BY MERCHANT_ID
    

    lunedì 12 marzo 2012 13:11
  • The last 2 solutions just work if the column MERCHANT_ID doesn't have null value, so I liked tkrasinger's way.

    Best Regards.


    lunedì 12 marzo 2012 13:20
  • To add to Sergey's comments why dynamic SQL of IF is a better solution, you may want to check any of the blogs I list below:

    Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
    Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
    Catch All Queries - short blog by Gail Shaw

    Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

    Option recompile

    Option recompile discussion thread


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Contrassegnato come risposta KJian_ lunedì 19 marzo 2012 06:32
    lunedì 12 marzo 2012 13:23
    Moderatore
  • Thanks for all your replies.

    How would solutions that use ISNULL and COALESCE functions work in this case?

    Essentially, if the variable is null, then the SELECT will look like this:

    SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = MERCHANT_ID 
    GROUP BY MERCHANT_ID

    Is it possible that MERCHANT_ID may not equal MERCHANT_ID? They're the same field.

    martedì 13 marzo 2012 14:44
  • If you don't have NULL in the Merchant_ID field, then the following will work

    WHERE MERCHANT_ID = COALESCE(@Merchant_ID, Merchant_ID)

    However, I proposed Sergey's suggestion and added all the blog links for a reason. Read at least the very first blog, it's a short one.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    martedì 13 marzo 2012 14:51
    Moderatore
  • Merchant_ID field will never be null since it's a primary key.

    My question is: Assuming that merchant_id will never be null and @merchantID is null, what's the difference between:

    SELECT sum(total) from MERCHANTS 
    WHERE MERCHANT_ID = MERCHANT_ID 
    GROUP BY MERCHANT_ID

    and

    SELECT sum(total) from MERCHANTS 
    GROUP BY MERCHANT_ID

    martedì 13 marzo 2012 15:04
  • They are the same and most likely will even generate the same plan, but please test it. Logically it's the same query and will return the same result.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    martedì 13 marzo 2012 15:36
    Moderatore