Click here to Skip to main content
15,513,571 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 14 Oct 2013

Stats

22.1K views
9 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.48/5 (8 votes)
15 Oct 2013CPOL2 min read
T-SQL code generation by using Dynamic SQL for routine tasks automation.

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.

Image 1

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:

SQL
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:

SQL
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: 

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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.

Image 2

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Administrator DraftKings Inc.
Ukraine Ukraine
SQL Server DBA/DB Developer with 10+ years of experience in SQL Server 2005-2019, Azure/GCP. Worked on high-load OLTP/DW projects and develops system tools for SQL Server. In depth understanding of SQL Server Engine and experience in working with big databases. Domain knowledge of ERP/CRM, crawlers, gambling and retail sales. Blogger, mentor and speaker at local SQL Server events.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Prasad Khandekar15-Oct-13 3:36
professionalPrasad Khandekar15-Oct-13 3:36 
GeneralMy vote of 4 Pin
Amol_B14-Oct-13 23:58
professionalAmol_B14-Oct-13 23:58 
good!
GeneralMy vote of 4 Pin
Brian A Stephens14-Oct-13 5:34
professionalBrian A Stephens14-Oct-13 5:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.