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