Dynamic SQL Going Haywire

  • Kanishka Basak

    Old Hand

    Points: 320

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

  • Phil Parkin

    SSC Guru

    Points: 244661

    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.

  • Jonathan AC Roberts

    SSCoach

    Points: 17319

    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

     

  • Phil Parkin

    SSC Guru

    Points: 244661

    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.

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

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

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

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4283

    And now I do...

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

  • Lynn Pettis

    SSC Guru

    Points: 442342

    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.

     

  • Kanishka Basak

    Old Hand

    Points: 320

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

  • chuck.hamilton

    Ten Centuries

    Points: 1395

    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.

  • beobregon

    Valued Member

    Points: 70

    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 3 months ago by  beobregon.
  • sgmunson

    SSC Guru

    Points: 110506

    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

  • Kanishka Basak

    Old Hand

    Points: 320

    Agreed, and that is also being discussed.

  • pdanes

    SSCrazy Eights

    Points: 8368

    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.

  • Kanishka Basak

    Old Hand

    Points: 320

    Yes you are correct..and so was I.

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

  • pdanes

    SSCrazy Eights

    Points: 8368

    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