Dynamic SQL - which would you use?

  • When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

    In this method, I'm setting a boolean column, either by ID if it is provided, or if it is not then we're setting all records that are not already set.

    Method 1: I have a different query depending on the parameter

    IF @ID IS NULL

    BEGIN

    UPDATE myTable

    SET ClearFlag = 1

    WHERE ClearFllg = 0

    END

    ELSE

    BEING

    UPDATE myTable

    SET ClearFlag = 1

    WHERE ID = @ID

    END

    Method 2: I include the parameter in the query

    UPDATE myTable

    SET ClearFlag = 1

    WHERE

    (@id IS NULL AND ID = @ID)

    OR

    (cleared = 0 AND @ID IS NULL)

    For a simple query like this, either way probably works just as well. When multiple parameters need to be taking into account I consider that the first way maybe executes faster but if any changes are required then they must be made in all the 'copied' queries. The 2nd method may not execute as quickly (thought I've not really tested), it can result in some complex clauses but means there is only one query.

    My question: Which way do you swing? Do you have any other ways? Any other comments welcome.

  • Neither. I'd use dynamic SQL.

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

    http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

    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
  • I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.

    I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

    Interested to hear opinions.

    Tony

  • tony@kenny.net (1/3/2013)


    I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.

    I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

    Interested to hear opinions.

    Tony

    Read the two articles Gail wrote and posted. She explains in detail about both of your types of queries and why dynamic sql will outperform either of the methods you posted. 🙂

    _______________________________________________________________

    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/

  • Hi,

    Sorry, I misunderstood the links and thought they were just part of the signature so didn't give them any attention.

    I've read the articles, thank you so much!

    Looks like I was doing things right many many years ago with MS SQL 6 and my decision to change a few years ago was actually a bad move (at least for certain scenarios)!

    Thank you for the great articles and for opening my eyes. I'm glad I got curious and asked!

    best regards,

    Tony

  • tony@kenny.net (1/3/2013)


    I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.

    I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

    Interested to hear opinions.

    Tony

    IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other. When future calls generate and execute the same dynamic T-SQL query, SQL Server will use the cached execution plan. So you could end up with separate plans for all possible variants of the dynamic T-SQL query, but as long as the execution plans remain in the plan cache, SQL Server will reuse them each time the same variants are executed.

    Jason Wolfkill

  • wolfkillj (1/4/2013)


    IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other.

    It'll always cache the plan, parameterised or not. Only very simple queries (no joins) qualify for auto-parameterisation. That said, the method in my blog posts uses a parameterised piece of dynamic SQL, meaning that queries of the same form with different parameter values will reuse plans.

    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
  • CELKO (1/4/2013)


    When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

    I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

    Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

    Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"

    Jeez, Joe... get over it.

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

    Change is inevitable... Change for the better is not.


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

  • CELKO (1/4/2013)


    When I need to execute code depending upon the values of parameters I often implement one of 2 methods:

    I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

    Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

    Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"

    Devoid as usual of any actual recommendation.

    What specifically should requestor do then?

    What specifically then should one do when a table has, say, 50 columns that can be arbitrarily filtered by users in self-directed queries, and you have to provide the supporting code?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • CELKO (1/4/2013)


    What specifically should requester do then?

    As I said, read books on basic Software Engineering. Yourdon and Constantine's STRUCTURED DESIGN is still classic. Chapters 6 and 7 are on Coupling and Cohesion. But you can find almost any book that will cover these fundamentals.

    What specifically then should one do when a table has, say, 50 columns that can be arbitrarily filtered by users in self-directed queries, and you have to provide the supporting code?

    Post DDL, sample data and clear specs. He did none of this. The problem is that he has no foundations. It is impossible to give him the education in a forum. The short answer is that you do not write code that allows a random stranger arbitrary access to data at run time. You need to have specs.

    Let me ask:

    1) Why do want to write a declarative language like SQL with procedural flow control? Do you like cursors? Loops? Recursive CTEs, which are both?

    2) Why are bit flags, loose cohesion and tight coupling a good way to program?

    3) Why would you have a design so bad that you need dynamic SQL? Are data element names changing at run time? Is the name of the procedure “No_Idea_What_I_Am Doing_Until_Run Time”? Or (more likely) are you doing display formatting the database layer and not the presentation layer?

    There's no "random stranger". That's just an idiotic, pointless slur.

    We're giving "power employees" of the company access to the company's data to derive business benefit.

    If you're incapable of really assisting with that in a real-world situation, just say so. Don't hide behind idiotic and irrelevant prattle.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • CELKO (1/4/2013)


    3) Why would you have a design so bad that you need dynamic SQL? Are data element names changing at run time? Is the name of the procedure “No_Idea_What_I_Am Doing_Until_Run Time”? Or (more likely) are you doing display formatting the database layer and not the presentation layer?

    He doesn't, no, no and no. (and there was nothing in the OP's question to suggest either of the latter two, nor is this a 'tight coupling' or 'loose cohesion' issue. This is a very common search pattern where users can enter any number of filters to search on (eg search by first name and/or last name and/or phone number and/or department).

    The recommendation of dynamic SQL in this case is solely to overcome a shortcoming in the SQL query optimiser.

    Did you even read the OP's question or did you just see the word 'dynamic' in the title and base your reply off that?

    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
  • tony@kenny.net (1/3/2013)


    My question: Which way do you swing? Do you have any other ways? Any other comments welcome.

    FWIW, this is how I'd probably do it. I like to get rid of NULLs as early as possible in a procedure so I don't have to use "Where X IS [NOT] NULL" syntax. Sometimes NULLs are unavoidable and of course this snippet assumes an INT key > 0.

    DECLARE @id INT

    SET @id = ISNULL(@id,0)

    IF @id < 0 SET @id = 0

    UPDATE myTable

    SET ClearFlag = 1

    WHERE

    (ID = @id)

    OR

    (ClearFlag = 0 AND @id = 0)

  • CELKO (1/4/2013)


    We're giving "power employees" of the company access to the company's data to derive business benefit.

    The power employees I work with regard me as the "Data Base Utility" who can get the raw or nearly-raw data in SAS data sets, assorted GIS formats, spreadsheets, etc. for their specialized power tools. That is why they are power users. My job is to hand them data that is clean, correct and immediately input into whatever they do. A lot of times their tasks are over my pay grade and security level.

    The average internal user only needs a fixed set of queries that match to his security level and organizational scope. These days, I hand off the data to a report server. This tier has changed the game quite a bit. Now there is a "report person" who slices, dices, rapes and pillages that data to make it look pretty.

    Middle managers at this level all claim that they need to see the same data in different colors, arranged in some special way, etc. as away to assert their personal power within the organization. Years ago, these guys would use FedEx for routine mail, have a FAX machine on their desk, demand huge green bar printouts, a reserved parking space, etc.

    Someone needs to write a book on report servers. I briefly worked for a company that made a Cube of basic Community College data, and then sold custom reports from it to the schools. That was their product.

    The external user gets an even more limited set of specific reports, such as your month account statement. On the bank's website, with a short drop-down list.

    Me, the accountants and the lawyers do not believe in letting people romp thru the data base.

    The average internal user only needs a fixed set of queries that match to his security level and organizational scope.

    Yes, but the "power users" -- as I explicitly referenced -- don't.

    You always set up straw men to knock down while ignoring, and not comprehending, the essential points.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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