The ever amazing Steve Jones (blog|twitter) is our host this month. And in case you didn’t know he also is currently running T-SQL Tuesday so you if you plan on joining in you might give him a thanks sometime. Between him and Adam Machanic (blog|twitter) we are up to 155 (almost 13 years) worth of blog parties. If you want to look at the archive go to http://tsqltuesday.com/.

Steve asked us to talk about dynamic code. Code that creates more code. This is a subject that I quite enjoy which made this subject a bit tricky for me. I’ve written quite a few posts on the subject over the years. I did finally come up with something though. Comments. Another subject near and dear to me. Dynamic code gets complicated pretty quickly. That of course means that it should have comments. And I’m going to say that you should not just comment your generator code, but the generated code as well.

As an example I was given a project a while back that required backing up a database, restoring it to multiple locations, one of which is in an availability group, fix the permissions, and make a small predictable data change. After the second time doing this it got annoying which means it quickly bot bumped up on my list of code to automate (or at least partially automate). In the end my design was to use dynamic SQL to create the SQL code necessary to do the restores, remove/add from the AG group, permissions, etc. There are other ways this could have been done for sure, this is just the way I chose at the time.

Because this is a fairly complex piece of code that I expect someone else will have to read/use at some point I put a comment block at the top explaining the basics and then commented the rest of the code with what was happening in each section, what different variables are for, etc. You know .. basic commenting. But since I was creating a piece of code for someone else to run I put comments inside the generated code. Note: The end goal is for this to be automated but I wanted to run it manually for a while to make sure it was bulletproof. I still feel like these comments would be useful after that point in case someone needs to make changes or debug a problem.

So for example the AG code looks something like this:

DECLARE @PrimaryName varchar(50) = 'PrimaryInstance'
DECLARE @SecondaryName varchar(50) = 'SecondaryInstance'

PRINT '/*********************************************'
PRINT ' This code uses SQLCmd code so make sure to '
PRINT ' turn on SQLCmd mode (In SSMS go to the Query '
PRINT ' menu, near the bottom) before running it. '
PRINT '*********************************************/'
PRINT '/* Remove DBName from the availability Group */'
PRINT 'USE master;'
PRINT 'GO'
PRINT 'ALTER AVAILABILITY GROUP [AGName]
     REMOVE DATABASE [DBName];'
PRINT 'GO'
--- Do some work here
PRINT
'/********************************************************
Add DBName back to AGName
YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. 
*********************************************************/
/* The database must be removed from the secondary instance
   so a new copy can be created when it''s added back. */
:CONNECT ' + @SecondaryName + '
USE [master];
GO
DROP DATABASE [DBName];
GO

/* Actually add DBName back to AGName. Fortunately this is 
   a small DB so it doesn''t take long. */
:CONNECT ' + @PrimaryName + '
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName]
     MODIFY REPLICA ON N''' + @SecondaryName + ''' WITH (SEEDING_MODE = AUTOMATIC);
GO
ALTER AVAILABILITY GROUP [AGName]
     ADD DATABASE [DBName];
GO

/* I''m not really sure why this is necessary but it was
   part of the code SSMS generated for me so I left it in.
   If you know better feel free to take it back out again.*/
:CONNECT ' + @SecondaryName + '
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;
GO'

And the output looks like this:

/*********************************************
 This code uses SQLCmd code so make sure to 
 turn on SQLCmd mode (In SSMS go to the Query 
 menu, near the bottom) before running it. 
*********************************************/
/* Remove DBName from the availability Group */
USE master;
GO
ALTER AVAILABILITY GROUP [AGName]
     REMOVE DATABASE [DBName];
GO
/********************************************************
Add DBName back to AGName
YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. 
*********************************************************/
/* The database must be removed from the secondary instance
   so a new copy can be created when it's added back. */
:CONNECT SecondaryInstance
USE [master];
GO
DROP DATABASE [DBName];
GO

/* Actually add DBName back to AGName. Fortunately this is 
   a small DB so it doesn't take long. */
:CONNECT PrimaryInstance
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON N'SecondaryInstance' WITH (SEEDING_MODE = AUTOMATIC);
GO
ALTER AVAILABILITY GROUP [AGName]
ADD DATABASE [DBName];
GO

/* I'm not really sure why this is necessary but it was
   part of the code SSMS generated for me so I left it in.
   If you know better feel free to take it back out again.*/
:CONNECT SecondaryInstance
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;
GO

Note that the output code is nicely commented and those comments are still useful in the source code as well.

In a case of great and kinda great minds thinking alike it turns out that Brent (blog|twitter) and I posted on the same topic.

https://www.brentozar.com/archive/2022/10/tsql2sday-start-your-dynamic-sql-with-a-comment/

What’s interesting is that while we both talked about comments in generated code, which is not exactly a large subject, we both came at it from different but complimentary (his word, not mine) angles. Brent talks about putting comments in bits of code (and advice on where in the code) so that you know what’s going on when you see it in the cache and need to find where it’s coming from. I spoke more on larger bits of code where you (or someone else) is likely to need to read the output.

It just goes to show that even with a very narrow topic it’s possible to have multiple blogs that are completely different.

A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.

DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
GO
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘GO’.

Completion time: 2022-05-18T16:53:57.5145414-05:00

Hu? I can’t use GO in my dynamic SQL? Well then how am I going to switch databases before I run my code?

Two mistakes here. I was going to say they were stupid mistakes, but let’s face it, everyone forgets things and gets stuck on something simple. So mistakes, but not stupid. Anyway.

• Mistake 1) You can’t use GO in dynamic SQL. That would be because GO is a batch separator and not actually part of T-SQL.
• Mistake 2) I am so in the habit of putting a GO after every USE that I forgot it’s not really necessary.

I banged my head against my desk for probably 3 hours, doing all kinds of weird searches, trying out all kinds of strange pieces of code, for something I knew was really really simple.

DECLARE @sql nvarchar(max);
SET @sql = N'USE DBName;
/* Do stuff here. */
SELECT * FROM sys.tables;'
EXEC sp_executesql @sql;

I ended up with a few takeaways from all of this.

• Habits can really mess you up if you aren’t careful.
• You don’t actually need a GO after a USE.
• Everyone makes mistakes. And frequently those mistakes are from some really simple stuff. No one is immune.

FYI I already knew that last one, but it’s one of those things that everyone should be reminded of periodically.

So here’s what happened. When I write dynamic SQL one of the steps I take is creating a script that works normally.

CREATE TABLE test (Id INT);
GO
CREATE VIEW vw_test AS 
	SELECT * FROM test;
GO

Next I declare a variable, put quotes around my command, and add sp_executesql. (If you are interested I’m actually skipping a few steps here. Such as incessant testing.)

DECLARE @sql nvarchar(max);
SET @sql = 'CREATE TABLE test (Id INT);
GO
CREATE VIEW vw_test AS 
	SELECT * FROM test;
GO'
EXEC sp_executesql @sql;
GO

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘GO’.
Msg 111, Level 15, State 1, Line 5
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘GO’.

Well that wasn’t what I expected. I spent something like 20 minutes trying to figure out how to use GO in the silly command. Remember how I said this should have been obvious to me? Well .. if you go back and read the post on GO that I linked to above you’ll see that GO isn’t actually a T-SQL command. It’s a SQL Server Management Studio command (among other programs). It tells SSMS to run the bit above it separately from the bit below. (It’s a Batch Separator.) Which is why it won’t work in dynamic SQL that only runs T-SQL commands. So in the end, I wasn’t able to create the table and view in the same command. Not that it was a huge issue once I got past my hang-up.

DECLARE @sql nvarchar(max);
SET @sql = 'CREATE TABLE test (Id INT);'
EXEC sp_executesql @sql;

SET @sql = 'CREATE VIEW vw_test AS 
	SELECT * FROM test;'
EXEC sp_executesql @sql;
GO

SELECT MyPass FROM #Password;

Now that’s odd right? The MyPass column in the first row looks empty, and in fact if I copy and paste it I don’t get anything. (The second row was added for control so you can see nothing there is nothing special about the table.) But look at this:

SELECT MyPass, len(MyPass) AS ColLen, 
     datalength(MyPass) AS ColDataLength 
FROM #Password;

So I have data! Trim functions didn’t affect the results at all. So what’s going on? My next step was this:

SELECT MyPass
	,ASCII(SUBSTRING(MyPass,1,1)) AS Char1
	,ASCII(SUBSTRING(MyPass,2,1)) AS Char2
	,ASCII(SUBSTRING(MyPass,3,1)) AS Char3
	,ASCII(SUBSTRING(MyPass,4,1)) AS Char4
FROM #Password;

Ok, now things are getting interesting. An ASCII value of 0? I’ve never heard of that. I honestly didn’t know it was possible. As it happens, yes, it’s a real value and in SSMS it does a few strange things.

You see how it makes the value disappear in the grid output right? In the text view it shows up but as a regular space.

SELECT MyPass FROM #Password;

The way I decided it was a regular space is by copying it and pasting it into a varchar variable and then checking the ASCII value of that first character again. This time I got back a 32 (a space).

On the other hand if you print the value to the message window it acts like the grid view and you only get values before the character?!? Try copying the output of this and paste it somewhere?!?

PRINT '|' + CHAR(0)+'abc' + '|';

In the end to copy the data over I used the ASCII values, put them into the CHAR function (one at a time) and created a string out of it. It was a bit hokey but it worked.

If you want to follow along with the above tests here is the code I used to create the temp table:

CREATE TABLE #Password (MyPass varchar(100));
INSERT INTO #Password VALUES (CHAR(0) + 'ABC'), ('ABC');

Note: None of this is stuff you should be using (at least deliberately) in production unless you are someone like Rob Volk (blog|twitter) who likes to use SQL tricks to make people cry (If you ever get to see his Revenge The SQL presentation do so!)

-- Setup
SELECT * INTO myColumns FROM sys.all_columns
ALTER TABLE myColumns ADD CONSTRAINT pk_myColumns 
	PRIMARY KEY (object_id, column_id)
CREATE INDEX ix_myColumns ON myColumns (name)

I’m using a very simple table, taking the data from sys.all_columns, with a pretty basic clustered primary key and index.

-- Code
DECLARE @name varchar(50) = 'name'
SELECT *
FROM myColumns
WHERE name = @name

DECLARE @sql nvarchar(1000) =
'SELECT *
FROM myColumns
WHERE name = @name'
EXEC sp_executesql @sql, N'@name varchar(50)', @name

The query plan for the first query

The query plan for the second query

What’s the difference?! Well, when you use a variable the optimizer uses an average value for the estimated number of rows (this is my best understanding and I could be miss-representing what’s actually happening but I don’t think so). When you use a parameter the optimizer can actually check the histogram and give an estimate based on the contents of the parameter. Here are the estimated and actual number of rows for each. 

Variable version:

Parameter version:

You’ll notice that the parameter version has a much closer (exact even) estimate of the number of rows. 

Also if you look at the query plans you’ll see that the variable version shows as a smaller part of the plan (7%/93%), and yet, when I timed them they were pretty close on time (590ms/731ms). I mention this not because the variable version is normally faster, but because I want to point out that these are estimates, and not necessarily all that accurate.

Not everyone has that nightmare? Just me? Really? Um .. well .. You know, let’s just forget I said anything.

Escaping strings is an important concept when working with computers. Not just DBAs, database developers, or even application developers. If you work with strings then this is important for you.

Here’s the thing. Computers are amazing tools, but they are very literal. A computer does exactly what it’s told. That means that if you tell the computer that you’re going to put single quotes (‘) on either side of a string then the computer is going to take that first single quote and think begining a string and the very next single quote it’s going to think end of the string, even if that’s not what you intended.

‘Let’s take a look’

So what can we do to help the computer out? It really depends on the language. In T-SQL we double up on the character.

‘Let”s take a look’

In C# it’s a backslash.

‘Let\’s take a look’

In HTML we have to convert certain characters into special codes. (Which I’m not going to demonstrate since this is HTML and it would be a pain in the neck.)

So why is this so important? Setting variables is a good example.

DECLARE @LastName varchar(50) = 'O''Conner'

Again, this is important regardless of language and, honestly, regardless of job. If you work with strings you need to not only understand this concept, but you need to know how the particular language you are working with handles escaping.

Awesome question! Let’s give it a shot.

When using TVPs, the first thing you have to do is define the table type.

USE Test
GO

CREATE TYPE MyTVPType AS TABLE
( Col1 INT PRIMARY KEY);
GO

The next we run the test. I’m using the TVP to check for values in a table. Similar to a dynamic IN clause.

USE Test
GO

-- Create a temp table with data to query from.
CREATE TABLE #DynamicTVPTest (Col1 INT);
INSERT INTO #DynamicTVPTest VALUES (1), (5), (67), (100), (301),
    (543), (997), (1111), (1245), (3356),
    (4295), (6546), (8342), (8567), (9000),
    (9265), (10045), (10321), (11456), (12545);
GO

-- Declare my TVP variable and add data.
DECLARE @MyTVP MyTVPType;
 
INSERT INTO @MyTVP VALUES (1), (5), (67), (100), (301),
    (543), (997), (3356), (9265), (11456), (12545);

-- Declare my dynamic SQL variable and add the query.
DECLARE @SQL nvarchar(4000);
SET @SQL = 
N'SELECT * FROM #DynamicTVPTest
WHERE Col1 IN (SELECT Col1 FROM @MyTVP)';

-- Run the whole thing.
EXEC sp_executesql @SQL, N'@MyTVP MyTVPType READONLY',
	@MyTVP;

And it worked! In further proof of the sp_executesql is just like creating a SP theory I got the following error if I forgot the READONLY clause in the second parameter.

Msg 352, Level 15, State 1, Line 15
The table-valued parameter “@MyTVP” must be declared with the READONLY option.

Which is exactly what I would have expected if I was running this as an SP and forgot the READONLY.

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.

Getting back on topic, the discussion I had with them gave me an idea for (what I think) is a really easy way to understand how to use input and output variables with dynamic SQL.

Let’s start with the basic syntax.

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT * FROM sys.databases'

EXEC sp_executesql @sql

Then if I want to pass in a single parameter.

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @sql nvarchar(max);

SET @sql = N'SELECT * FROM sys.databases ' + 
	' WHERE name LIKE @InternalNamePat';

EXEC sp_executesql @sql, N'@InternalNamePat nvarchar(50)', 
	@InternalNamePat = @NamePat

Quick point here. The first two parameters (the command, and the parameter list) must be Unicode (hence the N at the front). Also, notice how I named the variable inside the dynamic SQL “Internal”? That is completely not necessary. It can be exactly the same name as the external variable. I just did that to make it more obvious which variable was which.

Now let’s add an output variable.

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @MaxCreateDate datetime;
DECLARE @sql nvarchar(max);

SET @sql = N'SELECT @InternalMaxCreateDate = MAX(create_date) ' + 
	' FROM sys.databases WHERE name LIKE @InternalNamePat';

EXEC sp_executesql @sql, N'@InternalNamePat nvarchar(50), @InternalMaxCreateDate datetime OUTPUT', 
	@InternalNamePat = @NamePat, @InternalMaxCreateDate = @MaxCreateDate OUTPUT;

PRINT @MaxCreateDate

Now here you’ll see that I’m declaring another variable. And again I’m declaring it twice. First in the main part of the code, next in the second parameter of sp_executesql. You’ll also notice that in the second declaration I declared it as an OUTPUT variable. Then I also used OUTPUT in that last bit where I pass in the variable.

Now, for some, that’s going to be sufficient. For others, it’s still going to be highly confusing. So let’s look at the same thing from a slightly different viewpoint.

CREATE PROCEDURE dynamicsql (/* param 2 */ @InternalNamePat nvarchar(50), 
		@InternalMaxCreateDate datetime OUTPUT /* param 2 */ ) as
/* param 1 */SELECT @InternalMaxCreateDate = MAX(create_date) 
FROM sys.databases WHERE name LIKE @InternalNamePat /* param 1 */
GO

DECLARE @NamePat nvarchar(50) = 'm%';
DECLARE @MaxCreateDate datetime;

EXEC dynamicsql /* param 3 */ @InternalNamePat = @NamePat, 
	@InternalMaxCreateDate = @MaxCreateDate OUTPUT; /* param 3 */

PRINT @MaxCreateDate;

You’ll see this is a pretty basic CREATE PROCEDURE statement. You should also see that in the places where I have the /* */ comments I literally copied and pasted the parameters from the sp_executesql statement. (Yes, I realize what I’m calling Param 3 is actually parameters 3 and 4. In fact, it could be 3, 4, 5, 6, 7…. It’s still basically one piece of the process.)

And in the end it breaks down like this:

• Param 1: The code to run.
• Param 2: The parameter decleration for the dynamic SQL.
• Param 3: The parameter assignment when calling the dynamic SQL.

 
With the OUTPUT statement in appropriate places, as needed.

SELECT name, '"C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe" ' + 
	'/AssessmentName="DMA_Output" ' +
	'/AssessmentDatabases="Server=' + @@ServerName +
	';Initial Catalog=' + sys.databases.name +
	';Integrated Security=true" ' + 
	'/AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult ' + 
	'/AssessmentResultCSV="\\PathToSaveTo\'+REPLACE(@@ServerName,'\','_')+'\'+sys.databases.name+'.CSV"' +
	' > "\\PathToSaveTo\'+REPLACE(@@ServerName,'\','_')+'\'+sys.databases.name+'.LOG"'
FROM sys.databases WHERE state <> 6 -- exclude offline databases
  and database_id > 4 -- Exclude system databases

A couple of important points.

• I’m assuming that your install of the DMA is in the C:\Program Files\Microsoft Data Migration Assistant directory.
• I have the output going to the URL \\PathToSaveTo\ServerName. Obviously, you will need to replace the PathToSaveTo part to reflect where you want the output to go.
• And last but not least that last line can be removed if you want. It puts the output of the DMACMD (which is pretty verbose) into a log file just in case.
• Oh, and one more last but not least. The URL for the log file is also a \\PathToSaveTo\ so if you keep it you’ll need to change that one too.

This link has the breakdown of the parameters for the DMACMD in case you have different preferences. JSON output for example.