Dynamic SQL Going Haywire

  • Comments posted to this topic are about the item Dynamic SQL Going Haywire

  • It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation

    Rather than 'compilation', I'd suggest 'time of design'. T-SQL code does not get compiled.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin wrote:

    It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation

    Rather than 'compilation', I'd suggest 'time of design'. T-SQL code does not get compiled.

    The SQL does get compiled to a plan:

    https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15

     

  • Jonathan AC Roberts wrote:

    Phil Parkin wrote:

    It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation

    Rather than 'compilation', I'd suggest 'time of design'. T-SQL code does not get compiled.

    The SQL does get compiled to a plan:

    https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15

    Ah, true of course. I was thinking of compilation in terms of executable programs.

    Dynamic SQL also gets compiled to a plan, at which point the full text of the query is known, so I still think that 'time of design' is better.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I don't see an error message in the text, just whitespace.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • And now I do...

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Looking at the code you posted, correct, dynamic SQL was not required.  I have found numerous instances of improper use of dynamic SQL by developers with my current employer. It is a tool, and it seems when it is used it is because noone really looked at the SQL code that was being written. When you have a hammer, everything looks like a nail.

    Just one other thing, when writing dynamic SQL you should only use it for that part of the code that truly needs to be dynamic.  Use sp_executesql to run the dynamic SQL and pass in the data as parameters that are used in join and where conditions. As this data changes when used in building dynamic SQL you fail to reuse previous plans.

     

  • You are absolutely correct. In this case it was indeed not needed and that is why I had proposed and implemented it out of the dynamic code. Indeed true if things are not used correctly they can make you cry later...

  • EXEC () as written in the example is also susceptible to SQL injection attack. Consider the case where @lstChngDt (which based on the example is likely a varchar or nvarchar) gets set to something like  ' ''01/01/0001'' or 1 = 1'.

    Using sp_executesql with a parameterized query would be safer, expecially if you can't control what sets @lstChngDt. You can force @lstChngDt to be a datetime and prevent that from happening.

  • I think that developers need to know what is Dynamic SQL in order to avoid this kind of bad programming situations.

    • This reply was modified 1 year ago by  beobregon.
  • I hear you on dynamic SQL, but I think I'd have to be concerned about a system that can generate 62GB of data in tempdb as it may well be severely RAM constrained.   Also, a mere 62GB of free disk space is a DEV OPS problem that is, IMHO, inexcusably low...  I wouldn't run a SQL Server system without having at least 1 TB free space at the absolute minimum.   Allowing lower-cost items such as disk space and RAM to constrain your production system is a bad practice that can and sometimes WILL hurt you very badly...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Agreed, and that is also being discussed.

  • Maybe I'm missing something, but I have to wonder why this was ever done in dynamic. I use a fair amount of dynamic, but only after first trying without. Only after determining that it can't be done (or at least I don't know how to do it) without do I switch to dynamic. The command in this post is exceedingly simple, and I see no reason why dynamic was even considered, let alone implemented.

  • Yes you are correct..and so was I.

    It was never needed and its implementation caused a big problem. A unique world!!!

  • It's amazing what you sometimes find, but we probably shouldn't be too harsh on the author. I've certainly found my share of garbage in my own code on later reviews. Sometimes things were reasonable at first, and I didn't properly adapt my code when conditions changed, or I leave ballast from an experiment, because I forget to go back and tidy up after I get it working. We don't know what he had in mind when he did that, or what sort of advice he may have encountered while learning.

    Good job you picked up on it, though. Even crap code can be a learning opportunity.

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

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