Dynamic SQL can be an incredibly powerful tool in the pocket of a SQL Server developer. Developers frequently express amazement at the level of flexibility dynamic SQL offers, an astonishment at how quickly things get out of hand, and finally the humbling realization that such machinery nearly tore their limbs off. This process can be broken into five stages: The Five Stages of Dynamic SQL Grief. Here’s how to recognize where you, or someone you know, is at in their relationship with dynamic SQL.
Stage One: Denial
“THIS IS INCREDIBLE AND I’m going to use it everywhere!”
Stage one is where the developer will stare slack-jawed in amazement as the statement they just glued together like a words-cut-from-magazines collage not only compiles but returns results as expected. Suddenly, an entire universe of possibilities presents itself. What if we have one code path for admins and a separate one for power users? What if we just roll our INSERTs, UPDATEs, DELETEs, and SELECTs into one stored proc? That way we only have to make one proc per table.
But why stop there? What if we allow the user to pass in the name of the table they want to use? Then it’s one proc for the whole database! Oh this is going to rock people’s worlds when we roll it out.
SYMPTOMS OF DENIAL:
- Over-exuberance
- Disregard for/obliviousness to code performance, security, and the general lunacy of contemplating a universal stored procedure
- Not yet tired of typing single quotes (‘)
Stage Two: Anger
“HOW MANY SINGLE QUOTES do i need?”
Stage two sees the developer having struggled to get their code to production due to a multitude of issues, including but not limited to:
- Coding each of the many branches created by nested IF statements
- Testing and debugging each branch of the code
- Trial and error to figure out how many single quotes it takes to get the statement to compile
During this stage, the developer is irritable, but still resistant to the idea of simplifying anything. Their most important task is to get the code to run, and then ship.
SYMPTOMS OF ANGER:
- Refusal to believe complexity is part of the problem
- Profanity-laced tirades about single quotes
- Unwillingness to discuss the code with others
- Disinterest in security flaws
- Unfamiliarity with Erland Sommarskog
- Occasional lower back pain
Stage Three: Bargaining
“WHAT DO I HAVE TO CHANGE TO GET THIS TO run?”
Stage three begins when the developer can no longer deny being overwhelmed, and they begin to simplify their code. They start looking for areas of obvious frivolity to remove in order for it to test successfully. It is a turning point; they have reached high tide of complexity and must send their wildest ideas back out to sea in order to make their code viable again.
SYMPTOMS OF BARGAINING:
- Willingness to discuss code with others
- Receptivity to feature-cutting suggestions
- Continued disinterest in security flaws
- Admission that they have been overly ambitious in the application of dynamic SQL
Stage Four: Depression
“WHAT WAS I THINKING?”
Stage four is the low point for the developer writing dynamic SQL. At this stage, refactored code now works as designed. However, the security issues which the developer refused to address are now at the forefront, and cause a second round of rewrites. Forcing the developer to undergo a second set of rewrites is the proverbial kicking them while they’re down, and their spirits are at an all-time low.
SYMPTOMS OF DEPRESSION:
- A sudden interest in Erland Sommarskog
- Realization that many of their other dynamic SQL patterns were ill-conceived
- Extended work hours to fix their many, many bad ideas and security holes
- Increased interest in large quantities of ice cream and Netflix
Stage Five: Acceptance
“I was a fool, but now I know better.”
Stage five is the rebound stage for a developer’s initial encounter dynamic SQL. The fog of depression begins to lift as they work through the second rewrite process. This may seem counter-intuitive. It is not the rewrite that lifts the spirits of the developer, but rather the transformation from uneducated caterpillar to educated butterfly that improves their mood. They now have clearer insight into the consequences of their code decisions. They realize dynamic SQL is not a universal translator for turning business logic into a single script, and they will not make that mistake again.
SIGNS OF ACCEPTANCE:
- Willingness, if not eagerness, to discuss the topic of dynamic SQL
- Close familiarity with Erland Sommarskog
- Interest in other dynamic SQL references
- Sense of humility about their code abilities
- Return to nominal interest in ice cream and Netflix
If you encounter a SQL Server developer writing dynamic SQL, use this guide to identify which stage they are progressing through and treat them with empathy. They are going through a difficult time.
18 Comments. Leave new
Stage 2’s symptoms also includes the extensive use of PRINT statements to see what SQL you’re really generating.
Yes, this…… SO MUCH YES!
When the code made production and caused wrong data to be stored, being a DBA I encountered the following stages:
1) Denial
“My code doesn’t do that”
2) Withdrawal
“I can’t reproduce this error”
I haven’t seen other stages in my career. It was “a database problem” and therefore the DBA should reverse engineer what the mistake is. Did any of you encounter other stages ?
Great post BTW.
I like the portion in Stage 5: “They realize dynamic SQL is not a universal translator for turning business logic into a single script, and they will not make that mistake again.” May I pivot the conversation briefly to ask your opinion of encapsulating business logic in the database in general? My mentor beat it into me from a young age that databases were not where you codify business logic (and I agree with the sentiment) – just wondering your thoughts on that =)
PS – I use dynamic SQL for automating partition maintenance, but I feel dirty doing it.
Personally, I prefer having data logic in the database. If it’s a universal rule of the data that applies to all applications accessing the data, it goes in the database. My reasoning is that over my career as a developer I’ve been paid to write code in 4 languages, not counting T-SQL. Keep in my that the part of my career where my job was “Software Developer” only lasted 7 or 8 years. Programming languages change, databases usually don’t. I’ve worked with customers who’ve had the same general database since SQL Server 7, or even earlier.
Application specific logic goes in the app, universal data logic goes in the database.
That’s a tough one. In the past, I’ve put it in the database because I was more comfortable writing and testing it there than in the app. Since then, I’ve been leaning toward the app side because it’s really easy for less experienced developers to abuse triggers, cursors, and user-defined functions. (When I wrote business logic early in my career, I often did it with cursors and a lot of IF/ELSE branching.) I’d also rather be able to blame the app for being slow (and prove it) than have to worry about tuning business logic.
Jeremiah makes a good point, though. When it comes time to rewrite the app in whatever language is the new hotness, you may wish you had it in T-SQL instead.
Both very good points. I came from the admin side into DBA’ing, and it seems like the typical path to being a DBA is from the developer side, so I don’t have a hugely strong background in development (just my C++ days from high school/college). Even with that limited background, though, I notice that there are massive hoops you need to jump through to perform tasks that would be simple on another platform – plus I have to believe the performance of compiled code trounces that server side T-SQL execution.
That said, I really do like the idea of knowingly accepting that pain into my environment to ensure application changes don’t impact the core operation of the database/data – especially if there are other applications that rely on the data.
“Business Logic” is a general term, and can refer to logic that governs how data interacts with other data as well as logic that refers to how people interact with data.
Doesn’t it make sense to keep rules about how data interacts with data as close to the data as possible, and rules about how people interact with data as close to people as possible?
So, as a GENERAL rule….
–Data rules and logic go in the database.
–Interface rules and logic go in the application.
I am mainly working with an ERP software which brings the two worlds together. The business logic (and the whole application in fact, screens, reports, data input/output routines) is kept in the database but not as a set of stored procedures but as a metadata which is then read interpreted and executed by an application.
This gives amazing flexibility, removes lots of pains with the upgrade, but unfortunately performance is not what it could be as the app translates all data access to very simple pattern of SELECT * FROM table WHERE clause ORDER BY and like statements .
I just have to say- I have not laughed so hard in at least a year. Here I am by myself in my shop, breaking 100db volume with a full belly laugh. I needed that. Great post!!
Thanks, Jeff! Glad you enjoyed it.
Maybe a good 3A and be would be the realization that all the inline sql will pretty much ignore existing plans in cache and most indexes. 3B would be the most over utilized response from all developers “It worked in test”
I think there’s probably 9 circles of Dynamic SQL hell: https://en.wikipedia.org/wiki/Inferno_%28Dante%29
First Circle (Limbo)
Second Circle (Lust)
Third Circle (Gluttony)
Fourth Circle (Greed)
Fifth Circle (Anger)
Sixth Circle (Heresy)
Seventh Circle (Violence)
Eighth Circle (Fraud)
Ninth Circle (Treachery)
At some point the developer starts toying with nested levels of Dynamic SQL. If you thought single quotes with one level was hard… try going deeper into the inception nightmare
LMAO!
Great article!! Coincidentally I had just sent an email earlier that day to remind our developers of the evils of Dynamic SQL.
Maybe if you are feeling generous, you could drop a suggestion that CHAR(39) gets you out of single quote purgatory…
For the last 10 years I’ve worked on a data conversion team for one company. Because of the repetitive patterns of work that we do, and because they are all custom, one-time executions, we’ve been able to make very effective use of the meta data in SQL Server and dynamic SQL for the most repetitious stuff, some of it nested four and five levels deep. With this, we’ve been able to accomplish tons more than we otherwise could have had we written everything from scratch. We’ve also been through all the levels!
Not to say that dynamic SQL is for the faint of heart. It’s rather like a samurai sword. Used carefully and with practice by a skilled warrior, it can be a fantastic weapon. Wave it around willy-nilly like an amateur and you’ll cut your arm off.
Great post!
If you ever feel forced, having no choice but to use dynamic SQL, because SQL Server wont allow you to bind a @variable to a database name or a database object name (e.g. a table), then CREATE SYNONYM can come to the rescue!
E.g.:
IF (condition)
BEGIN
CREATE SYNONYM Source FOR LinkedServer1.Database1.dbo.Table1
END
ELSE
BEGIN
CREATE SYNONYM Source FOR LinkedServer2.Database2.dbo.Table2
END
and then:
SELECT * FROM Source WHERE… ORDER BY…
or whatever you fancy!
I use this all the time, for example for my DTAP staging scripts.