Nada to Speaker: Demystifying and Diminishing Dynamic SQL Difficulties

3

August 20, 2018 by Kenneth Fisher

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.

3 thoughts on “Nada to Speaker: Demystifying and Diminishing Dynamic SQL Difficulties

  1. Liz Love says:

    You did a great job Kenneth; I enjoyed your presentation and learned some more that I didn’t already know about dynamic SQL; thanks! This is only your second time speaking there? Then I’ve seen both although I’ve only had sporadic attendance through the years. Last time was SQL security if I’m not mistaken. You did a great job that time too. Thanks! 🙂

  2. […] I did a presentation on dyanmic SQL. In the presentation I pointed out the similarity of using sp_executesql to creating a stored […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,469 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: