Last Thursday I spoke at NTSSUG (North Texas SQL Server User Group). This is my local group (only 1.5 hours away from me) and my second time speaking for them. I don’t speak frequently so I’m always more than a bit nervous, but the crowd was great, laughed in the right spots, asked some really good questions and all around had some great energy. This post is primarily to share the Powerpoint and scripts I used during my demo but since I don’t like doing a post that is just links to some files I’m adding some of the content below as well.

Demystifying and Diminishing Dynamic SQL Difficulties PPT and Scripts

One of the ideas that I mentioned is that there are two types of Dynamic SQL as I see it. The processes that I use to create each are pretty similar but of course not quite the same.

One to Many

Using a single piece of code (typically a query) to generate multiple lines of SQL.

Example

SELECT 'ALTER DATABASE ' + QUOTENAME(name) + 
'SET COMPATIBILITY_LEVEL = 130'
FROM sys.databases
WHERE compatibility_level < 130

Process

1. Write the SQL statement first.
2. Write the query that pulls the result set you want.
3. Add the command to the query and convert it into to a string.
4. Convert any literals that need to become dynamic into fields.
   1. Double any quotes
   2. Replace anything you want to make dynamic with ‘++’ then plug in the column/formula.
   3. Include schema names
   4. QUOTENAME is your friend.
5. Copy the results to a query window.
6. Test
   1. Parse
   2. Run a SINGLE command, preferrably in a transaction to test for correctness.
   3. Run the rest of the commands in a transaction if possible.
7. After each failed test make one (or sometimes two) corrections and test again.

Many to One

Multiple lines of code are used to construct a single piece of SQL.

Example

DECLARE @SQL nvarchar(500);
DECLARE @SchemaName nvarchar(50) = 'sys';
DECLARE @TableName nvarchar(50) = 'databases';
SET @SQL = 
N'SELECT *
FROM ' + QUOTENAME(@SchemaName) + '.' +
QUOTENAME(@TableName);
--EXEC sp_executesql @SQL;
PRINT @SQL;
GO

Process

1. Write the SQL statement first.
2. Declare a variable to store the code, and convert the code into a string.
3. Convert any literals that need to become dynamic into fields.
   1. Double any quotes
   2. Declare any variables that are going to need
   3. Replace anything you want to make dynamic with ‘++’ then plug in the column/formula/variable.
   4. Include schema names
   5. QUOTENAME is your friend.
4. Copy the results to a query window.
5. Test
   1. Parse
   2. Run a SINGLE command, preferrably in a transaction to test for correctness.
   3. Run the rest of the commands in a transaction if possible.
6. After each failed test make one (or sometimes two) corrections and test again.