A Case for Using Dynamic SQL

Dynamic SQL is often talked about in hushed tones in dark corners. Many SQL practitioners judge it to be far too risky to use in everyday life. I've heard people say that it's dangerous because you never know what you are executing; that it is prone to SQL injection; and that it is easier (and safer) to write static SQL.

All of those objections are true and reasonable, yet I believe that there are really good reasons to use dynamic SQL. They all revolve around needing the flexibility to deal with changing objects without creating large amounts of technical debt.

What Is Dynamic SQL?

Dynamic SQL is when you construct your SQL command into a string and then execute that command. Here is a quick example that would yield the same results:

SELECT TOP 10 * FROM Customers;
DECLARE @TableName VARCHAR(50) = 'Customers'
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT TOP 10 * FROM ' + @TableName
EXEC SP_EXECUTESQL @SQL

You may look at this and wonder why you would ever trade one line of SQL for 4, especially with the risks involved. It has to do with flexibility.

Why Is Dynamic SQL Considered Dangerous?

Dynamic SQL is not 100% safe, and the objections to it are valid. But, with some basic precautions, it is possible to make it as safe as any other SQL and reap the benefits of its flexibility. Let's look at the objections.

Lack of Oversight for What You Are Executing

It is really hard to debug something that you can't just execute line by line. One missed quote and your dynamic SQL will splat like an egg off a counter.

As a way to counter this, I always include an @Debug flag. If the flag is set, it prints out the constructed SQL so that you can examine it and execute it independently.

You should always make sure that your input data is clean as well, which brings us to ...

Prone to SQL Injection

Dynamic SQL has the reputation of being more prone to a SQL injection attack than explicitly coded static SQL; however, this is not the case.

Any string that is stored and passed into any type of SQL (static or dynamic) runs the risk of carrying injection code. The (in)famous Little Bobby Tables example shows what happens when you don't sanitize your SQL.

There are ways to minimize the risk of injections when it comes to dynamic SQL: always use SP_EXECUTESQL to execute the assembled SQL and also use parameters with correct data types when possible. This doesn't limit the danger from string data types, so you can always check to make sure your pieces and parts don't contain something like '; drop table students;'.

Easier to Write Static SQL

I've heard many SQL developers say over the years that writing static SQL to manage tables is job security. In today's fast-paced world, the time needed to write, test, and deploy static SQL for constantly changing databases can be hard to justify when you can use dynamic SQL and never have to touch the code again.

The Case for Dynamic SQL

Dynamic SQL can, with appropriate safeguards, make queries flexible without needing recoding when the database changes. To give you an idea of how this could be used, we'll do an example:

We have a business that franchises out widget-cranking carts. The widgets get cranked out and sold on the spot, one per customer. These carts can be placed anywhere – in malls, shops, recreational facilities, on streets. All one has to do to use them is to apply to the main business, get assigned a seller ID, and check out a cart. At the end of the day, all carts are returned and the data elves download all of the information from the carts and place it into a database in the schema 'Shops'. There is one table per seller, and the seller ID is the last 2 digits of the table name.

On average, the business assigns out 10-15 new seller IDs per day. Not all sellers from the previous day check out a cart, and we don't know who has sold what. The big boss wants us to access all the seller information from the day before and know how many units were sold by each seller.

We are going to use Dynamic SQL for this. The reasons are:

• We don't know the names of all the tables we need to query in the database on any given day.
• If we were to explicitly code all of the sellers into a procedure or view, we would never be able to pull the data in the overnight timeframe.
• The data elves are using bulk copy program (BCP) utility to create the tables and they can't wait for us to stand up tables manually for them.

So, let's code this.

DECLARE @Debug BIT = 1
DECLARE @ParmDefinition NVARCHAR(MAX)
DECLARE @SaleDate DATE
DECLARE @SellerID INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @TableName VARCHAR(50)
DECLARE @TotalWidgets INT 

DECLARE cursorTables CURSOR For
SELECT i.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES i
WHERE i.TABLE_SCHEMA = 'Shops'
ORDER BY i.TABLE_NAME

OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SellerID = TRY_CONVERT(INT, RIGHT(@TableName, 2))
	IF @SellerID IS NOT NULL -- it will be null if for some reason the last two characters are not numeric
	BEGIN
		SELECT @SaleDate = DATEADD(DAY, -1, GETDATE())
		SET @SQL = 'SELECT @TotalWidgetsOutput = COUNT(1) FROM [Shops].[' + @TableName + '] WHERE SaleDate = @SaleDateInput'
		SET @ParmDefinition = N'@SaleDateInput DATE, @TotalWidgetsOutput INT OUTPUT';


		IF @Debug = 1
			PRINT @SQL
		ELSE
		BEGIN
			EXEC SP_EXECUTESQL @SQL, @ParmDefinition, @SaleDate, @TotalWidgetsOutput = @TotalWidgets OUTPUT
				
			INSERT INTO Sales.DailySales(SellerID, TotalWidgets, SaleDate) VALUES (@SellerID, @TotalWidgets, GETDATE())
		END
	END
	FETCH NEXT FROM cursorTables INTO @TableName
END
CLOSE cursorTables
DEALLOCATE cursorTables

Results

Running with @Debug = 1, we see:

SELECT @TotalWidgetsOutput = COUNT(1) FROM [Shops].Guildenstern_10] WHERE SaleDate = @SaleDateInput
SELECT @TotalWidgetsOutput = COUNT(1) FROM [Shops].[Rosenkrantz_53] WHERE SaleDate = @SaleDateInput
SELECT @TotalWidgetsOutput = COUNT(1) FROM [Shops].[Hamlet92] WHERE SaleDate = @SaleDateInput

Running with @Debug = 0, the Sales.DailySales table looks like this:

Some Notes on the Code

I am using an input parameter for SoldDate to demonstrate how it is used. However, since it is populated by an internal SQL function of type date, it is not vulnerable to injection.

I am not using an input parameter for the TableName because it is coming from the INFORMATION_SCHEMA object and is one of the built-in SQL objects. In order to get injected SQL in there, one would have to either name the table or columns with the injection code.

As you can see, writing the code once using dynamic SQL allows us to adapt for the number of tables and variations in the names of the tables without having to write more code. Using a debug flag, we can see what is being generated, and by taking some basic precautions with parameters, we limit the possibility of SQL injection.

Dynamic SQL isn't something that should replace all static SQL, but it is a good tool to have in your SQL toolbox when you need to account for unforeseen variations.