Dynamic SQL (aka Ad Hoc SQL) is SQL code that is generated at runtime. It’s quite common. Nearly every system I’ve supported in the past 30 years uses it to some degree, some more than others.
It can also be a particularly nasty pain point in a lot of systems. It can be a security vulnerability. It can be difficult to troubleshoot. It can be difficult to document. And it can produce some wickedly bad results.
When I started including T-SQL unit testing into my solutions a few years ago, Dynamic SQL quickly rose to the top of the pile of code to test. A pattern quickly emerged, and has gone largely unchanged since then. Here’s how it works:
- First, bear in mind that I am adamant about not allowing application code to generate T-SQL code. Stored procedures provide an immensely useful API into the database, and I will enforce the exclusive use of stored procedures as that interface in every situation I possibly can. So this solution naturally builds on that stipulation.
- When generating Dynamic SQL in stored procedures, I will create the procedures in pairs. The first procedure calls the second, which generates the Dynamic SQL. That second procedure then returns that result as a string to the first procedure. The first one then executes the string that it receives.
- I write my unit test to execute the second procedure only, and then do a simple string comparison to a control string. If the strings match, then the unit test passes.
And that’s it.
Here’s an example, which is part of a painfully over-complicated demo project I put together for my session at the 2022 Summit:
CREATE OR ALTER PROCEDURE [so].[Archive_SelectPagedData]
@ArchiveId BIGINT,
@Offset BIGINT,
@Batch BIGINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @stmt NVARCHAR(MAX);
IF (@ArchiveId IS NULL)
OR NOT EXISTS (SELECT 1
FROM [so].[Archive]
WHERE [ArchiveId] = @ArchiveId)
THROW 60001, 'ArchiveId', 0;
IF NOT EXISTS (SELECT 1 FROM [so].[Archive])
THROW 60004, 'Archive', 1;
EXEC [so].[Archive_SelectPagedData_s]
@ArchiveId = @ArchiveId,
@Stmt = @stmt OUT;
EXEC sp_executesql
@stmt = @stmt,
@params = N'@offset BIGINT, @batch BIGINT',
@offset = @Offset,
@batch = @Batch;
END;
GO
CREATE OR ALTER PROCEDURE [so].[Archive_SelectPagedData_s]
@ArchiveId BIGINT,
@Stmt NVARCHAR(MAX) OUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@columns NVARCHAR(MAX) = N'',
@fullName NVARCHAR(256),
@orderBy NVARCHAR(MAX) = N'',
@tableType INT;
SELECT
@fullName = N'[dump].' + QUOTENAME(a.[ForumName] + N'_' + tt.[TableTypeName]),
@tableType = a.[TableType]
FROM [so].[Archive] AS a
INNER JOIN [so].[TableType] AS tt
ON a.[ArchiveId] = @ArchiveId
AND a.[TableType] = tt.[TableTypeId];
SELECT
@columns += N'
' + QUOTENAME(cd.[ColumnName]) + N',',
@orderBy += CASE cd.[IsPrimaryKey] WHEN 1 THEN QUOTENAME(cd.[ColumnName]) + N',' ELSE N'' END
FROM [so].[ColumnDefinition] AS cd
WHERE cd.[TableType] = @tableType
ORDER BY cd.[ColumnIndex];
SELECT @columns = LEFT(@columns, LEN(@columns) - 1)
SELECT @orderBy = LEFT(@orderBy, LEN(@orderBy) - 1)
SELECT @Stmt = N'/* so.Archive_SelectPagedData */
SELECT{columns}
FROM {fullName}
ORDER BY {orderBy}
OFFSET @offset ROWS FETCH NEXT @batch ROWS ONLY;';
SELECT @Stmt = REPLACE(@Stmt, N'{fullName}', @fullname);
SELECT @Stmt = REPLACE(@Stmt, N'{columns}', @columns);
SELECT @Stmt = REPLACE(@Stmt, N'{orderBy}', @orderBy);
RETURN 0;
END;
GO
Test:
/* ARRANGE */
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE
@compare NVARCHAR(MAX),
@id BIGINT,
@stmt NVARCHAR(MAX);
SELECT @compare = N'/* so.Archive_SelectPagedData */
SELECT
[Id],
[TagName],
[Count],
[ExcerptPostId],
[WikiPostId]
FROM [dump].[foo_Tags]
ORDER BY [Id]
OFFSET @offset ROWS FETCH NEXT @batch ROWS ONLY;';
BEGIN TRANSACTION;
EXEC [so].[Archive_Upsert]
@ArchiveId = @id OUT,
@ForumName = N'foo',
@TableType = 6,
@SourcePath = N'bar';
/* ACT */
EXEC [so].[Archive_SelectPagedData_s]
@ArchiveId = @id,
@Stmt = @stmt OUT;
/* ASSERT */
IF (@compare != ISNULL(@stmt, N''))
BEGIN
PRINT ISNULL(@stmt, N'(null)');
THROW 50002, N'so.Archive_SelectPagedData_s_1', 0;
END;
ROLLBACK TRANSACTION;
GO
The first thing to note about this approach is that the strings must 100% match, right down to the white space. That may seem like an unrealistic obstacle at first. But my experience hasn’t supported that assumption. In fact, chasing down the few cases in which tests are failing due to small things like an extra space can often result in the discovery of larger issues that might have otherwise gone unnoticed.
Enforcing such consistency also aids in managing your execution plan reuse. Side Note: Don’t forget to check your Optimize for Ad Hoc Workloads setting!
This approach also aids documentation and troubleshooting. The unit test requires an exact instance of the string being generated. With unit testing like this, you have examples of what the code should be creating right in front of you!
And, if the existing unit tests aren’t enough to identify a problem, then the same pattern can be used while troubleshooting. Once an issue has been identified this way and rectified, then you’re already most of the way to creating a covering test for the new case!
So there ya go… unit testing for your Dynamic SQL. Enjoy!