How to Design, Build and Test a Dynamic Search Stored Procedure

  • Dwain Camps

    SSC Guru

    Points: 86883

    Comments posted to this topic are about the item How to Design, Build and Test a Dynamic Search Stored Procedure


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • davoscollective

    SSCertifiable

    Points: 6325

    Great article, thanks for sharing. I've used similar constructs for flexible SSRS reports. I learned a few nice tricks, I particularly like the @debug and the line numbers, very nice.

    Based on Celko's war on dynamic SQL, I wonder if you'll get a response here too. Surely that would be a mark of success 😉

  • Dwain Camps

    SSC Guru

    Points: 86883

    davoscollective (11/5/2013)


    Great article, thanks for sharing. I've used similar constructs for flexible SSRS reports. I learned a few nice tricks, I particularly like the @debug and the line numbers, very nice.

    Based on Celko's war on dynamic SQL, I wonder if you'll get a response here too. Surely that would be a mark of success 😉

    And thank you sir for taking the time to read the article and I'm happy to hear you learned a couple of tricks. I also have to apologize for the SQL formatting issues. I've got an email in to the editor to correct that.

    If Joe Celko were to chime in with his thoughts on dynamic SQL, I'd consider it a badge of honor. Dynamic SQL certainly has its place, and this is most certainly one of them. There are cases where it can be overused and/or abused, so a healthy and productive discussion of that (here if necessary) would be most welcome.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Phil Parkin

    SSC Guru

    Points: 243861

    Dwain does it again - nice article sir!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • IdRatherCodeIt

    SSC Veteran

    Points: 214

    Nice article,

    For truly dynamic behavior, do you think moving that responsibility to coding might reduce verbosity, error prone string concatenation, and allow more flexible dynamic searches?

    I'm talking about solution s like dynamic linq in c#.

    Or using predicates with linq.

    That type of solution would be testable using formal unit tests

  • Scott Abrants

    SSCommitted

    Points: 1503

    A very nice and complete article - thank you for your efforts in creating this.

    I like the Excel idea to manage some test cases and data, interesting approach.

    Scott

  • odeddror

    SSC Eights!

    Points: 878

    Hi there,

    How do you fix the null value?

    Execute dbo.Shipment_Tracking @custID = null

    This will give all records

    Thanks,

    Oded Dror

  • Jeff Moden

    SSC Guru

    Points: 995150

    Phil Parkin (11/5/2013)


    Dwain does it again - nice article sir!

    Ditto that! Nicely done, Dwain. Great explanations and revelation of code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • sqldriver

    SSChampion

    Points: 10440

    Awesome article. I particularly like using NULLIF to check the search parameters.

    Also a reminder that I wish the display more than 8k characters SQL Spackle were default server behavior. Thanks Dwain.

  • Dwain Camps

    SSC Guru

    Points: 86883

    Phil, Scott, Jeff and Erik,

    Thanks for stopping in to read the article and offer your comments. I hope you find the content useful in your future endeavors.

    And to Jeff, special thanks to you sir for taking the time to slog through such a long article clearly not targeted at someone with your level of expertise. Your critical eye is always welcome of course. Keeps me honest.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86883

    IdRatherCodeIt (11/5/2013)


    Nice article,

    For truly dynamic behavior, do you think moving that responsibility to coding might reduce verbosity, error prone string concatenation, and allow more flexible dynamic searches?

    I'm talking about solution s like dynamic linq in c#.

    Or using predicates with linq.

    That type of solution would be testable using formal unit tests

    IRC,

    Well I guess I'd have to say "it depends" (to quote one of my SQL Heroes). I think what I did is pretty darn flexible already. Recall what I said at the end, about this being a small (about 20%) subset of the actual functionality delivered by the Production SP.

    The thing I don't like about queries embedded in front end apps is that the code is quite obfuscated. Not only (when in an SP) can you pretty clearly read the underlying SQL even though it is embedded in strings, it opens the code up to scrutiny by those that may not have C# skills (like DBAs). The more scrutiny a code undergoes, the likely better it will be. While you could still uncover the underlying code using Profiler, that's an unnecessary hassle as far as I'm concerned.

    Furthermore, I think it depends on the confidence you have in your C# development teams. If they're really good and you have plenty of resources available to support an application through multiple platform releases, you may be OK. SQL doesn't change much from version to version. With the exception of assignment on the DECLARE statements, the SQL I've used here is pretty compliant back through SQL 2000 I'd think (although that statement is untested).

    Finally, when your testing team uncovers an error, in C# it may require recompiling and an application redeployment, which in production may mean some app downtime. A fix to an SP can be deployed without that, and I'd venture to say finding the bug and fixing it might take a fraction of the time (if in an SP). This also allows retesting the SP using just the @debug facility.

    Just a few things to think about.

    Edit: An additional point that occurred to me is that if you're talking about web apps here (and I presume you are), constructing very large SQL query strings and then sending them through for execution on the server side increases the amount of data transferred between the client-side and the server. In most cases I'd think that the call to execute the SP would be a much shorter string. Possibly a minor performance improvement.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86883

    odeddror (11/5/2013)


    Hi there,

    How do you fix the null value?

    Execute dbo.Shipment_Tracking @custID = null

    This will give all records

    Thanks,

    Oded Dror

    That is by design. Passing a NULL to @CustID is like passing no value, and allowing the default behavior of the SP cover it, i.e., return all Shipment records.

    If you are asking how you would check a Shipment for @CustID IS NULL (like in my challenge question at the end), you'd need to do that by specifically looking for "missing status MDE." In other words someone has passed in the @MissingStatus = 'MDE' parameter, so look especially for that and change the appending of the NOT EXISTS code fragment to "AND CustID IS NULL" on the Shipment record.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • odeddror

    SSC Eights!

    Points: 878

    What about

    execute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null

    Will give all record regardless null

    Thanks,

    Oded Dror

  • Dwain Camps

    SSC Guru

    Points: 86883

    odeddror (11/5/2013)


    What about

    execute [dbo].[Shipment_Tracking] @CustID = 'Apple', @ReferenceNo = null

    Will give all record regardless null

    Thanks,

    Oded Dror

    Have you tried it? It should only return records with @Custid = 'Apple'

    Because @ReferenceNo is passed as NULL, that AND is not added to the WHERE clause.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • odeddror

    SSC Eights!

    Points: 878

    of course I try it will give you two record for Apple one with ReferenceNo SO 1111 and one with null

    Look the code doesn't answer the null

Viewing 15 posts - 1 through 15 (of 46 total)

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