Post reply

Improve Stored Procedure

  • April 1, 2010 at 3:26 am

    #217363

    Hi,

    I converted one of my stored procedures which used to use dynamic SQL to one that did not however since the change the procedure appears to run slower. The syntax for the new procedure is:

    @aCHAR(2)

    @b-2CHAR(2),

    @CCHAR(2),

    @dCHAR(2),

    @eCHAR(2)

    SELECT *

    FROM mtTable

    WHERE a = ISNULL(@a, a) AND

    b = ISNULL(@b, b) AND

    c = ISNULL(@c, c) AND

    d = ISNULL(@d, d) AND

    e = ISNULL(@e, e)

    The previous version of the procedure would create the WHERE clause based upon whether any of the parameters were not NULL i.e. if the parameter was NULL it would not appear in the WHERE clause.. Does anyone know of a better way to achieve the above?

    Thanks

  • April 1, 2010 at 3:44 am

    #1143783

    U need to do it using dynamic SQL mate..

    please go through the following thread which is similar to ur request and find this post

    http://www.sqlservercentral.com/Forums/Topic892483-338-1.aspx#bm892554

  • April 1, 2010 at 4:01 am

    #1143790

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

    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

  • April 1, 2010 at 4:08 am

    #1143792

    Ok, thanks all for the responses, looks like I had it right the first time! Oh well, I'll stick with dynamic SQL.

    Thanks

  • April 1, 2010 at 4:26 am

    #1143802

    David, as in Gail's reply, please go thro that article which Gail wrote about the SQL-Injection probelms with dynamic sql and how to counter them.. excellent article that.. and take care while u use dynamic SQL...

  • April 1, 2010 at 9:53 am

    #1144159

    David-155102 (4/1/2010)

    The previous version of the procedure would create the WHERE clause based upon whether any of the parameters were not NULL

    The previous implementation was probably pretty close to optimal 🙂

    When you upgrade to SQL Server 2008, the situation changes (at least from Service Pack 1 Cumulative Update 5 onward). Your construction can be made to perform extremely well, at the cost of being recompiled every time it is called.

    See Dynamic Search Conditions in T-SQL (2008) for full details.

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

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