SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Nada to Speaker: Demystifying and Diminishing Dynamic SQL Difficulties

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...