May 7, 2020 at 12:00 am
Comments posted to this topic are about the item Dynamic SQL Going Haywire
May 7, 2020 at 12:30 pm
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 absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 7, 2020 at 1:28 pm
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:
May 7, 2020 at 1:40 pm
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:
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 7, 2020 at 2:55 pm
I don't see an error message in the text, just whitespace.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
May 7, 2020 at 2:57 pm
And now I do...
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
May 7, 2020 at 3:57 pm
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.
May 7, 2020 at 4:22 pm
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...
May 7, 2020 at 6:41 pm
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.
May 7, 2020 at 9:32 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2020 at 8:40 am
Agreed, and that is also being discussed.
May 9, 2020 at 10:53 am
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.
May 9, 2020 at 1:04 pm
Yes you are correct..and so was I.
It was never needed and its implementation caused a big problem. A unique world!!!
May 9, 2020 at 9:34 pm
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply