Post reply

dynamic WHERE clause

  • November 9, 2012 at 1:16 pm

    #284615

    Hello,

    I have a query that have a dynamic WHERE clause. The query's WHERE clause is built based on the web page that a user is on. I was wondering, what is the best approach to deal with this kind of situation in SQL Server 2005? Everything is the same in the query but the WHERE clause.

    Should I have 1 stored procedure with IFs? Should I have a stored procedure for each WHERE clause? Something else?

    WHERE (B.BusinessStatus='A')

    -----------------------------------------------------------------------------------------

    -- WHERE clause for MG Industry

    -----------------------------------------------------------------------------------------

    AND ( (B.MarketGuideIndustryNumber=727)

    OR (B.BusinessId=13425908))

    -----------------------------------------------------------------------------------------

    -- WHERE clause for SIC

    -----------------------------------------------------------------------------------------

    --AND (B.BusinessId IN ((SELECT DISTINCT BusinessID FROM BusinessSICCodes WITH (NOLOCK) WHERE SICCode='7374')

    -- UNION

    -- (SELECT BusinessId=244)))

    -----------------------------------------------------------------------------------------

    -- WHERE clause for NAICS

    -----------------------------------------------------------------------------------------

    --AND (B.BusinessId IN ((SELECT DISTINCT BusinessID FROM BusinessNAICSCodes WITH (NOLOCK) WHERE NAICSCode='541519')

    -- UNION

    -- (SELECT BusinessId=244)))

    -----------------------------------------------------------------------------------------

    -- WHERE clause for User's Portfolio

    -----------------------------------------------------------------------------------------

    --AND (B.BusinessId IN ((SELECT DISTINCT BusinessId FROM UserBusinesses WITH (NOLOCK) WHERE UserId=19601)

    -- UNION

    -- (SELECT BusinessId=244)))

    -----------------------------------------------------------------------------------------

    -- WHERE clause for a Personal Folder

    -----------------------------------------------------------------------------------------

    --AND (B.BusinessId IN ((SELECT DISTINCT BusinessId

    -- FROM FolderBusinesses F WITH (NOLOCK)

    -- INNER JOIN PortfolioFolders P WITH (NOLOCK) ON F.FolderId=P.FolderId

    -- WHERE P.UserId=19601

    -- AND P.FolderId='3')

    -- UNION

    -- (SELECT BusinessId=244)))

    Thank you in advance!

    Denis

  • November 9, 2012 at 1:36 pm

    #1557790

    Take a look at this article by Gail Shawn on "Catch all queries"

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 9, 2012 at 1:44 pm

    #1557793

    I second reading the article by Gail. Also, why all the NOLOCK hints? Are you aware of the possible risks using that?

    _______________________________________________________________

    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/

  • November 9, 2012 at 1:52 pm

    #1557796

    There is no BEST way to do anything since aspects of each situation will vary.

    I like your idea of IFs in a stored procedure since each option may be optimized separately.

    I use sp_execute when any/all parameters may be used in a dynamic WHERE clause.

    And remember that SQL is also cached so a stored procedure won't get you better performance.

    BTW, no need to do a SELECT DISTINCT when using an IN clause. It is implicitly done.

  • November 12, 2012 at 6:17 am

    #1558140

    Luis, thank you for your reply! Gail ROCKS 🙂 I've been reading her blog for a while now! I think I have an idea now how to deal with this.

    Denis

  • November 12, 2012 at 6:19 am

    #1558141

    Sean,

    Yes, I am aware about the possibility of dirty reads while using the NOLOCK hint. I really don't care about them in this case. I needed avoid any locking.

    Denis

  • November 12, 2012 at 6:24 am

    #1558144

    Bill,

    Thank you! As always -- it depends and there is no best way 🙂 Didn't know about the SELECT DISTINCT, do you know where I could read about this? I'd like to understand it!

    Appreciate your input!

    Denis

  • November 12, 2012 at 7:11 am

    #1558163

    DenisT (11/12/2012)

    Sean,

    Yes, I am aware about the possibility of dirty reads while using the NOLOCK hint. I really don't care about them in this case. I needed avoid any locking.

    Denis

    It can be far worse than just uncommitted data. You can get duplicates or missing data. If possible, using isolation levels would be far better. I am at home today so I don't have any links. It was an eye opener for me some of the major pitfalls of that hint. If nobody beats me to it I will post some links tomorrow.

    _______________________________________________________________

    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/

  • November 12, 2012 at 7:47 am

    #1558183

    Sean,

    Didn't know that either! That would be great! I really appreciate it.

    Denis

  • November 12, 2012 at 9:39 am

    #1558215

    Here is one of the articles that explains the deeper issues with NOLOCK.

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/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/

  • November 12, 2012 at 2:08 pm

    #1558318

    Thank you, Sean. It's good to know!

    Denis

  • November 12, 2012 at 2:20 pm

    #1558320

    Bill Talada (11/9/2012)

    I like your idea of IFs in a stored procedure since each option may be optimized separately.

    If you're talking about straight queries within IF blocks, they won't be optimised separately. Everything in a proc gets optimised when the proc first runs, regardless whether the statement can be reached with the specified set of parameters

    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

  • November 12, 2012 at 2:24 pm

    #1558324

    Hi Gail,

    Thanks for your input! Good point, the procedure is cached as a whole with IF conditions in the plan.

    Denis

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

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