T-SQL Tuesday #155 –The Dynamic Code Invitation

T-SQL Tuesday is the blog party started by Adam Machanic (b|t) over a decade ago and is now maintained by Steve Jones (b|t) on tsqltuesday.com. Each month, lots of people blog their thoughts on a common topic.

This month’s T-SQL Tuesday is all about the use of dynamic SQL. I use Dynamic SQL a lot in my role as a Data Engineer / DBA.

There are frequent occasions where the same code needs to be run across multiple Databases.
For this, the undocumented Stored Procedure “sp_MSforeachDB” is ideal as it does just that. There are blog posts out there covering how to use it / offering better alternatives, but this isn’t one of them.

I wanted to talk about a problem that became apparent while using “sp_MSforeachDB” and, more importantly, how I overcame it.

The examples that I have used are extremely simple. Writing dynamic SQL needs special care and attention to embedding and nesting quotes. Actually, that might have been a better article, but we’re here now.

sp_MSforeachDB Basics

Using “sp_MSforeachDB” is pretty simple. Build a String with the code that you want to be executed, prefix the string with “USE [?] ” and calling the Procedure using the String as a Parameter.
When the Procedure runs through each of the Databases, the ? Is replaced with the current Database name.
e.g.

DECLARE @dynSQL nvarchar(2000);
SELECT @dynSQL = 'SELECT DB_NAME()';
SELECT @dynSQL = 'USE [?] ' + @dynSQL;
SELECT @dynSQL;

EXEC sp_MSforeachdb @Command1 = @dynSQL;
GO

Issues Encountered

I had a requirement to Create a Stored Procure on all Databases on a Server, so I thought about using “sp_MSforeachDB”, but then I hit an issue.

DECLARE @dynSQL nvarchar(2000);
SELECT @dynSQL = 'CREATE PROCEDURE USP_NumberOne AS BEGIN SELECT 1 END;';
SELECT @dynSQL = 'USE [?] ' + @dynSQL;
SELECT @dynSQL;

EXEC sp_MSforeachdb @Command1 = @dynSQL;
Actually, I hit an error message:
(1 row affected)
Msg 111, Level 15, State 1, Procedure USP_NumberOne, Line 1 [Batch Start Line 16]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

The issue is that in order for sp_MSforeachDB to be of any use, it needs to have that “USE [?] ” as the first statement in the Batch. Create Statement has a similar requirement.
My first thoughts were to put a “GO” statement after “USE [?] “, but this failed too.
After a bit of searching, I found a few articles suggesting that the “GO” statement needs to be on a line by itself, so I tried adding CHAR(13) and CHAR(10) – the ASCII characters for CRLF.

Guess what happened – it still failed.
The next challenge was to use a different approach to get the new line characters into the dynamic SQL.

Writing SQL to write SQL

Another use for dynamic SQL is for creating more SQL, and let’s face it you can never have enough SQL.

I have written about this before. One of the things I particularly like about this approach is that you get to review every line before running it. Great for proper testing and debugging before automation.

The code that I wrote is as follows:
DECLARE @quote NCHAR(1) = CHAR(39);
DECLARE @crlf NCHAR(2) = CHAR(13) + CHAR(10);
SELECT 'USE ' + QUOTENAME([name]) + CHAR(13) + CHAR(10) + ' GO ' + @CRLF + 'CREATE PROCEDURE [spTest] AS BEGIN SELECT ' + @quote + 'Nothing to see here' + @quote + ' END;'
FROM sys.databases
WHERE [name] NOT IN ('master','model','msdb')

Next step was to copy the results and paste them into another SSMS window for execution.
Guess what happened!

(1 row affected)
Msg 111, Level 15, State 1, Procedure USP_NumberOne, Line 1 [Batch Start Line 16]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

It’s getting boring now. Turns out that even though I’ve carefully crafted the statements with CRLF, they’ve been lost in the Copy / Paste operation.

Finishing Touches

There’s a setting in SSMS that determines whether CR/LR is retained when performing Copy / Paste operations. By default, it isn’t set, but by putting a check in the box, everything becomes a little bit different.
Click on the “Tools” menu, then “Options” and select the “Results\Grid” Pane

 

 

 

 

 

Now open a new Window in SSMS for the change to take effect. I know that you shouldn’t need to, but you do (and I lost a bit of time while writing this Blog Post trying!)

Now, copy the generated SQL Script from the Results grid and paste it into another SSMS Window.
This revised code should appear beautifully formatted, and it’ll work when executed.

 

Hope this helps someone else, and thanks for the T-SQL Prompt!

Nigel.