How to Generate T-SQL Code by Using Dynamic SQL?

Introduction

This tip shows the examples of automating such tasks as views creation, query generation, and consolidated tables creation by using Dynamic SQL.

Background

It's no news that native IntelliSense support for SSMS was first implemented in SQL Server 2008. With the release of SQL Server 2012 native IntelliSense became somewhat more functional however its performance was still not perfect - that was mainly due to the specific of my work which required a lot of T-SQL coding.

On the one hand, third-party tools which offered expanded IntelliSense functionality helped me out a lot and allowed to make T-SQL coding more efficient.

For example, SQL Prompt and SQL Complete can automatically complete the syntax of ALTER, INSERT, EXEC statements and can expand columns for SELECT statements.

To execute the expansion of a selected statement, it is enough to press the TAB key when the hint comes up.

However, in my case, there was the necessity for automatic code generation for more specific statements.

Let's take a closer look at several examples of the actual tasks, which I managed to automate by using Dynamic SQL.

Using the Code

1. Automatic Views Creation

Let's start with a plain sailing, for example, try to implement automatic generation of view definition according to the table name:

DECLARE 
	  @SQL NVARCHAR(MAX)
	, @table_name SYSNAME
	, @view_name SYSNAME

SELECT 
	  @table_name = '[dbo].[BlogUsers]'
	, @view_name = '[dbo].[vw_BlogUsers]'

SELECT @SQL = 
	CASE WHEN OBJECT_ID(@view_name, 'V') IS NOT NULL 
		THEN 'ALTER' 
		ELSE 'CREATE' 
	END + ' VIEW ' + @view_name + '
AS
	SELECT ' + STUFF((
	SELECT CHAR(13) + '		 , t.[' + c.name + ']'  
	FROM sys.columns c WITH(NOWAIT)
	WHERE c.[object_id] = OBJECT_ID(@table_name, 'U')
	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 6, '') + '
	FROM ' + @table_name + ' t'

PRINT @SQL
EXEC sys.sp_executesql @SQL 

After the script execution, the view will be created automatically:

CREATE VIEW [dbo].[vw_BlogUsers]
AS
	SELECT t.[UserID]
	     , t.[GoldBadges]
	     , t.[SilverBadges]
	     , t.[BronzeBadges]
	     , t.[TotalBadges]
	FROM [dbo].[BlogUsers] t  

2. Automatic Query Generation

Let's move on and write a script which will generate UNPIVOT query. Note that this approach is not a universal solution. That is why it has certain restrictions – unpivoted columns should have compatible data-types between them. Granting this restriction, let's create a sample table: 

IF OBJECT_ID ('dbo.BlogUsers', 'U') IS NOT NULL
	DROP TABLE dbo.BlogUsers

CREATE TABLE dbo.BlogUsers
(
	  UserID BIGINT PRIMARY KEY
	, GoldBadges INT NOT NULL DEFAULT 0
	, SilverBadges INT NOT NULL DEFAULT 0
	, BronzeBadges INT NOT NULL DEFAULT 0
	, TotalBadges AS GoldBadges + SilverBadges + BronzeBadges
)

INSERT INTO dbo.BlogUsers (UserID, GoldBadges, SilverBadges, BronzeBadges)
VALUES
	(1, 5, 4, 0),
	(2, 0, 9, 1),
	(3, 2, 4, 10)

Let's create a dynamic query for the table by selecting columns which are not included in the primary key and are not identity and computed:

DECLARE 
	  @SQL NVARCHAR(MAX)
	, @object_name SYSNAME

SELECT @object_name = '[dbo].[BlogUsers]'

SELECT @SQL = '
SELECT * 
FROM ' + @object_name + '
UNPIVOT (
	Value FOR Code IN (' + STUFF((
	SELECT ', [' + c.name + ']'
	FROM sys.columns c WITH(NOWAIT)
	WHERE c.[object_id] = OBJECT_ID(@object_name)
		AND INDEX_COL(@object_name, 1, c.column_id) IS NULL
		AND c.is_computed = 0
		AND c.is_identity = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')
) unpiv'

PRINT @SQL
EXEC sys.sp_executesql @SQL

The execution results – the UNPIVOT query:

SELECT * 
FROM [dbo].[BlogUsers]
UNPIVOT ( 
	Value FOR Code IN ([GoldBadges], [SilverBadges], [BronzeBadges])
) unpiv  

3. Creation of Consolidated Tables

The process of creating tables with a large count of similar columns is quite a tiring task which can be easily automated with the help of Dynamic SQL.

The following script is an example:

DECLARE 
	  @SQL NVARCHAR(MAX)
	, @object_name SYSNAME 
	, @key_column SYSNAME

SELECT 
	  @object_name = '[dbo].[ManufactureByYears]'
	, @key_column = 'ManufactureID'

SELECT @SQL = '
IF OBJECT_ID (''' + @object_name + ''', ''U'') IS NOT NULL
   DROP TABLE ' + @object_name + '

CREATE TABLE ' + @object_name + ' (
	  [' + @key_column + '] INT PRIMARY KEY' + (
	SELECT CHAR(13) + '	, [Year' + CAST(2000 + sv.number AS VARCHAR(4)) + '] INT'
	FROM [master].dbo.spt_values sv
	WHERE sv.[type] = 'p'
		AND sv.number BETWEEN 1 AND 13
	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') + '
)'

PRINT @SQL
EXEC sys.sp_executesql @SQL

The result of its execution is the following query:

IF OBJECT_ID ('[dbo].[ManufactureByYears]', 'U') IS NOT NULL
   DROP TABLE [dbo].[ManufactureByYears]

CREATE TABLE [dbo].[ManufactureByYears] (
	  [ManufactureID] INT PRIMARY KEY
	, [Year2001] INT
	, [Year2002] INT
	, [Year2003] INT
	, [Year2004] INT
	, [Year2005] INT
	, [Year2006] INT
	, [Year2007] INT
	, [Year2008] INT
	, [Year2009] INT
	, [Year2010] INT
	, [Year2011] INT
	, [Year2012] INT
	, [Year2013] INT
)

Over time, the amount of such scripts has increased and the process of finding each of them for using again became more time consuming. That prompted the solution to add the most frequently used ones as snippets.

As a conclusion, I would like to highlight the idea that the abilities of dynamic SQL are numerous and are not limited to the above mentioned examples. I really hope that the described possibilities of Dynamic SQL will help readers to optimize and facilitate the most routine operations.