In the SQL Server community, most examples of dynamic T-SQL code are plagued with a severe readability problem. They're hard to read because dynamic T-SQL code isn't formatted, so you can't easily grasp the command and query structure. Concatenation operators and the necessary doubling of single quotes obfuscate the dynamic query even more. And sometimes several parts of the query need to be generated from the same variable, which ends up adding even more concatenations. Tracing the query to see what was generated isn't much help because the query isn't formatted.
Related: T-SQL Best Practices, Part 1
The unreadability of dynamic T-SQL code discourages DBAs and developers from using it in large T-SQL solutions. Plus, taking advantage of free solutions contributed by the SQL Server community is hampered because people can't easily grasp the code generated.
To make dynamic T-SQL code more readable, I've been using several techniques since the introduction of the nvarchar(max) data type in SQL Server 2005. This data type is vital to my techniques because string size is no longer a limitation to query formatting, and text manipulation is as easy as with other nvarchar() data types.
The CompareRows stored procedure in Listing 1 illustrates my techniques. It compares rows from two tables, from two queries, or from one table and one query, then lists the nonmatching records.
CREATE PROC CompareRows @ColList nvarchar(max) , @Srctab AS sysname = '' , @TgtTab AS sysname = '' , @SrcQry nvarchar(max) = '' , @TgtQry nvarchar(max) = '' , @SrcWhereClause nvarchar(max) = '' , @TgtWhereClause nvarchar(max) = '' , @SrcDB AS sysname = '' , @TgtDB AS Sysname = '' , @SrcInstance AS sysname = '' , @TgtInstance AS sysname = '' AS BEGIN DECLARE @sql nvarchar(max) SET @sql = ' WITH SrcRows AS () , tgtRows As ( ) , UnionOfDataSetsToCompare as ( Select "(source) [ ].[ ]. " as DataSetId , FROM SrcRows UNION ALL Select "(target) [ ].[ ]. " as DataSetId , FROM TgtRows ) SELECT MAX(DataSetId) as DataSetid , FROM UnionOfDataSetsToCompare GROUP BY HAVING MAX(DataSetId) = MIN(DataSetId) ORDER BY ' -- Assume some behavior for missing parameters. IF @SrcDB = '' SET @SrcDB = DB_NAME() -- Current database if no @SrcDb IF @TgtDB = '' Set @TgtDB = @SrcDB -- Same database if no @tgtDg IF @TgtTab = '' Set @TgtTab = @Srctab -- Same table name if no @TgtTab IF @TgtWhereClause = '' SET @SrcWhereClause = @TgtWhereClauseAnchor -- BEGIN CALLOUT A IF @SrcQry <> '' SET @sql = REPLACE (@sql, ' ', @SrcQry) -- If @SrcQry is supplied, the following will result in no changes -- because the tag will be gone. SET @sql = REPLACE ( @sql, ' ', ' SELECT FROM [ ].[ ]. ' ) IF @TgtQry <> '' Set @sql = REPLACE (@sql, ' ', @TgtQry) -- If @TgtQry is supplied, the following will result in no changes -- because the tag will be gone. Set @sql = REPLACE ( @sql, ' ', ' SELECT FROM [ ].[ ]. ' ) -- END CALLOUT A -- Replace tags. SET @sql = REPLACE (@sql, ' ', @ColList) SET @sql = REPLACE (@sql, ' ', @Srctab) SET @sql = REPLACE (@sql, ' ', @Tgttab) SET @sql = REPLACE (@sql, ' ', @SrcWhereClause) SET @sql = REPLACE (@sql, ' ',@TgtWhereClause) SET @sql = REPLACE (@sql, ' ', @SrcDB ) SET @sql = REPLACE (@sql, ' ', @TgtDB ) -- Remove the linked server syntax part if not specified. IF @SrcInstance = '' SET @sql = REPLACE (@sql, '[ ].', '') SET @sql = REPLACE (@sql, ' ', @SrcInstance IF @TgtInstance = '' Set @sql = REPLACE (@sql, '[ ].', '') SET @sql = REPLACE (@sql, ' ', @TgtInstance ) -- Replace double quotes with real single quotes. SET @sql = REPLACE (@sql, '"', '''') PRINT @sql -- Show the query for debugging purposes. EXEC (@sql) -- Execute the query. END
After I explain how this stored procedure works, I'll walk you through the techniques it uses to make the dynamic T-SQL code easier to read.
How CompareRows Works
As the CompareRows stored procedure demonstrates, comparing rows can be done very efficiently using a UNION ALL statement to put together rows from a source and a target, provided that you add the DataSetId column in which the values identify the source or target. A common table expression named UnionOfDataSetsToCompare unites rows from the source and target, adding the DataSetId value. A GROUP BY clause is then applied on the columns being compared and the HAVING clause compares minimum and maximum aggregates on the generated DataSetId column to detect which rows have no matching counterpart in the source or target. The end result is a rowset of all rows that have no matching record, with the DataSetId column describing where each row is from.
The stored procedure compares the data in the rows for the columns you specify. It has 11 parameters, most of which are optional because CompareRows is flexible when it comes to specifying the source and target rows to compare. Rows can come from the same table or different tables, from the same database or different databases, or from the same SQL Server instance or different instances. You can also filter out subsets of rows to compare by including WHERE clauses that apply to the source or target table. Rows can even be generated by queries.
Here are the stored procedure's 11 named parameters:
- @ColList -- Used to specify which columns to include in the comparison. The primary key columns must be included. The columns' names need to be in a comma-separated list.
- @SrcTab -- Used to specify the name of the source table. The schema must be included. (Add brackets if required.)
- @TgtTab -- Used to specify the name of the target table if it isn't the same as the source table. The schema must be included.
- @SrcQry -- Used to specify the source query. When used, @SrcTab is ignored and the source rows come from this query. The database and schema must be included in the FROM clause.
- @TgtQry -- Used to specify the target query. When used, @TgtTab is ignored and the target rows come from this query. The database and schema must be included in the FROM clause.
- @SrcWhereClause -- Used to provide the WHERE clause for the source rows if applicable.
- @TgtWhereClause -- Used to provide the WHERE clause for the target rows if applicable.
- @SrcDB -- Used to specify the source database if it isn't the current database.
- @TgtDB -- Used to specify the target database if it isn't the same as the source database.
- @SrcInstance -- Used to specify the linked source server if it isn't the local server.
- @TgtInstance -- Used to specify the linked target server if it isn't the local server.
Listing 2 shows an example of how to use CompareRows to compare the Contacts table in the AdventureWorks database on one instance against the same table in a restored copy of the AdventureWorks database on another instance. As you can see, there's no need to specify the target table because it has the same name as the source table. The same applies for target database.
EXEC CompareRows @ColList = 'ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate' , @Srctab = 'Person.Contact' , @SrcDb = 'AdventureWorks' , @TgtDb = 'AdventureWorksCopy' .., @SrcInstance = 'ASQL9' .., @TgtInstance = 'ASQL10'
Listing 3 shows an example of how to use the stored procedure to compare the rows generated from two queries. You can download CompareRows by clicking the download link at the top of this page.
EXEC CompareRows
@ColList = 'ContactID, NameStyle, Title,
FirstName, MiddleName, LastName,
Suffix, EmailAddress, EmailPromotion,
Phone, PasswordHash, PasswordSalt,
rowguid, ModifiedDate'
, @SrcQry = 'SELECT * FROM
ASQL9.AdventureWorks.Person.Contact
WHERE phone LIKE "440%"'
, @TgtQry = 'Select * FROM
ASQL10.AdventureWorksCopy.Person.Contact
WHERE phone LIKE "440%"'
How to Make Dynamic T-SQL Code Easier to Read
I use several techniques to improve the readability of dynamic T-SQL code. They include giving T-SQL strings an indented query structure, using replacement tags, using double quotes, and replacing a set of tags with other tags.
Give T-SQL strings an indented query structure. In T-SQL, a literal string might start with the opening quote on one line and end many lines later with the closing quote. Every line change (i.e., line feed or new line) in between becomes part of the string. You can dynamically add more line changes to an existing T-SQL string by using the CHAR or NCHAR function with the parameter of 10, which is the ASCII value for a line feed. This generates a line feed that both the SQL Server T-SQL parser and SQL Server Management Studio (SSMS) interpret and present as a line change. The immediate advantage of this technique is that the code is much more readable because it retains the line changes and indents.
However, SSMS limits PRINT output to 8,092 characters. To work around this limit, I created the Tools.PrintSqlCode stored procedure, which you can find in the 141080.zip file. In that file, you'll also find the Tools.SqlCodeLinesInResultSet function, which is more suitable when you want to log the generated code in a work table. Both split the code at line changes, making multiple code lines.
Use replacement tags. To avoid concatenations, I use replacement tags, which are similar to XML element tags. The replacement tags' names match those of the named parameters, so it's easier to understand what the code is doing. Then, at the end of the code, I use T-SQL's REPLACE string function to modify the query in the dynamic T-SQL code. The REPLACE function is handy because you can include extra code or logic in its parameters if needed. For example, you can include code that translates a date into string or use a CASE statement to conditionally generate the code that will replace the tag.
Let's look at a simple example. Dynamic T-SQL code that backs up a database would traditionally look something like this:
DECLARE @sql nvarchar(max) SET @sql = 'Backup database ['+@MyDb+'] TO disk = ''c:\backups\'+@MyDb+'.bak'' WITH description = ''Backup of '+@MyDb+ ' at ' + CONVERT (nvarchar, getdate() 121)+ ''''
To make it more readable, you can use
DECLARE @sql nvarchar(max) SET @sql = ' BACKUP DATABASE [] TO disk = "c:\backups\ .bak" WITH description = "Backup of at " '
Then, at the end of the code, you use two statements:
SET @sql = REPLACE (@sql, '', '@MyDb') SET @sql = REPLACE(@sql, ' ', CONVERT(nvarchar, getdate(), 121)
The first statement replaces
Use double quotes. Another technique is to use double quotes instead of doubling single quotes. This is my favorite technique. Double quotes are a single character and much easier to read than doubling the single quote, so coding is less prone to errors. Then, I use the REPLACE function to switch the double quotes with real single quotes, with code such as:
SET @sql = REPLACE(@sql, '"', '''')
I usually place this REPLACE function call after all other REPLACE statements.
Replace a set of tags with other tags. When generating complex queries, I use the REPLACE function to replace a set of tags with other tags. This helps to reduce the conditional logic in multiple code generation cases. I used this technique to make the CompareRows stored procedure work either from rows generated by queries in @SrcQry and @TgtQry or from rows supplied directly from the tables specified in @SrcTab and @TgtTab. For example, at callout A in Listing 1 (below), you can see that if @SrcQry is supplied,
Make Your Dynamic T-SQL Solutions More Readable
Without these four techniques, the CompareRows stored procedure would be an unreadable piece of junk code because it's a fairly large and complex dynamic T-SQL solution. You can find more examples of these techniques in the free YourSqlDba script at www.grics.qc.ca/yoursqldba. This script includes stored procedures for database maintenance and database cloning that my colleagues and I have written using these techniques. I hope that someday these techniques will be widely adopted for the sake of helping people share their dynamic T-SQL solutions.
