This months’ invitation from Steve Jones asks us to write about producing SQL dynamically in some form or another. We can create and execute SQL dynamically within our procedures but what about dynamically creating it from other sources before passing it into SQL Server?
The quick
I have to admit that Steve’s mention of using Excel certainly rings true for me. There have been countless times when I’ve been passed an Excel document which needed to be loaded – typically as a one-off task – and I’ve succumbed to Excel as the solution to save some time.
For these instances where it’s an ad-hoc task and simply a case of getting things off my plate it was much more efficient to use something like Excel or Notepad++ to wrap a collection of values into a SQL statement to achieve the goal.
If we took an example in Excel, the formula would typically looks something like this:
="INSERT INTO dbo.ToDoList ([Priority], [Description]) VALUES (" & A2 & ", '" & B2 & "')"
As you can tell this isn’t the most secure implementation of dynamically created SQL in the slightest but since I’m the one building, validating and running it then it serves it’s purpose. And it really has, a lot.
And the dirty
That’s not to say this approach hasn’t backfired of course. With larger volumes of data it can be easy to miss some values which may cause syntax issues. Lets suppose we add Replace Andy's poor dynamic SQL code into the list, how might that turn out?
INSERT INTO dbo.ToDoList (Priority, Description) VALUES (1, 'Replace Andy's poor dynamic SQL code')
That doesn’t look good. Unfortunately we don’t get intellisense in Excel to help spot these types of issues. This is the reason why the validation or a dry run with a local copy of the data can be key to catching these types of issues.
This could certainly be worked around with a SUBSTITUTE thrown in there in this instance, but if the data starts getting more complicated this type of formula can become unwieldy. Dealing with erroneous characters, NULL or empty strings, new-line characters, trimming text, or defaulting values – they all add noise into any formulas if you try to work around them.
Multiply those fixes up with more fields and you’ve got some unreadable spaghetti code starting to form. At that point it’s likely time to formalise the process or at least consider something a bit more robust such as the Import / Export wizard in Management Studio where you can get the data into a native SQL format to work with.
A little perspective
Taking a step back to the wider topic of dynamically created SQL statements, the above example shows the criticality of putting security at the forefront when considering this approach. Whilst this is a trivial example, a wider production implementation of dynamic SQL could leave the environment open to injection which may lead to a whole host of issues.
With that said, I’m not diminishing dynamic solutions at all – they’re a fantastic tool to wield and they can help to solve some complex challenges in relatively trivial ways. One of my favorite use-cases of this is simply injecting a comment into a query to cache multiple plans for different parameters which may be used for example.
As they say, with great power… and all that.
Andy Brownsword 