SQL Server: Generate table through Dynamic SQL / Stored Procedures

Version: SQL Server 2019 Developer Edition

Dynamic SQL is the technique which allows users to write SQL statements as strings and execute them. SQL Server supports the Dynamic SQL feature and SQL developers can utilize this feature to write flexible and programmatic queries. However, there is still a lot of controversy over SQL Injection and performance in Dynamic SQL. We will discuss these issues in a later post. In this post, we will create a table dynamically using Dynamic SQL.

First, let's create a table to store schema and input data.

-- CREATE Table statement
CREATE TABLE [dbo].[TableSchema](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[TableName] [varchar](128) NOT NULL,
	[ColumnName] [varchar](256) NOT NULL,
	[IsPrimaryKey] [bit] NULL,
	[DataType] [varchar](32) NOT NULL,
	[DataLength] [int] NOT NULL,
	[Nullable] [bit] NOT NULL,
	[InsertedDtTm] [datetime2](7) DEFAULT(GetDate())
)

-- INSERT data
INSERT INTO [dbo].[TableSchema] (TableName, ColumnName, IsprimaryKey, DataType, DataLength, Nullable) 

VALUES
('PersonSlim','BusinessEntityID','1','bigint','','0'),
('PersonSlim','PersonType','','varchar','64','1'),
('PersonSlim','Title','','varchar','32','1'),
('PersonSlim','FirstName','','varchar','64','1'),
('PersonSlim','LastName','','varchar','64','1'),
('PersonSlim','Email','','varchar','128','1')

The below chart briefly explains the [TableSchema] table above.

TableName	The name of table
ColumnName	The name of field
IsprimaryKey	If the field is primary key, then 1, else 0
DataType	The data type of the field
DataLength	The data length of the field
Nullable	If the field allows NULL values, then 1, else 0

After saving the data, you must use it to build a CREATE TABLE statement. Before moving to the next step make sure you have permission to create or generate a table (CREATE TABLE statement is a DDL(Data Definition Language.). Once you have confirmed that, we will create a CREATE TABLE statement as a string and put it in VARCHAR type variable and run the Dynamic SQL through the EXECUTE function.

We will create a CREATE TABLE statement as a string with the following template here.

CREATE TABLE [dbo].[PersonSlim]  (

    /* Part 1-Columns */
    [<Columns>],   

    
    /* Part 2-Hash rows (concatenating all columns) */

    [_HashValue] AS HASHBYTES(''SHA2_256'', CONCAT_WS(''|''' + <HashValue> +')), 

PRIMARY KEY CLUSTERED (

    /* Part 3-Primary Key */
    [ <Primary Key> ] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF

    , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

PART1 - [<Columns>]: concatenating field definitions

DECLARE @tablename VARCHAR(50) = 'PersonSlim'
DECLARE @cols VARCHAR(MAX) = '';

SELECT @cols = @cols + '[' + [ColumnName] + ']' + ' ' + '[' + [DataType] + ']' 
		+ IIF([DataType] = 'varchar', '('+ CAST([DataLength] AS VARCHAR) +')',  '') + ' ' 
		+ IIF([Nullable] = 1, 'NULL', 'NOT NULL')
		+ ',' + CHAR(13)+CHAR(10)
FROM [dbo].[TableSchema]
WHERE TableName = @tablename
ORDER BY [id]

PRINT @cols -- Debug

Point1) @cols  = @cols + [<string | fields>]

There are several ways to concatenate values from multiple rows into single text. I will use “+=”(String Concatenation Assignment) to concatenate values. For reference, SET @var += ‘test_string’ is equal with @var = @var + ‘test_string’.

Point2) CHAR(13)+CHAR(10)

CHAR(13) means carriage return, and CHAR(10) means line break. Here, it was used to improve the readability of the PRINT @cols output statement.

Point3) IIF function is a function of TSQL and can be thought of as a condition statement of general programming.

IIF ( boolean_expression, true_value, false_value ) 

Execute) The above query will output the string below:

PART2 - [_HashValue] AS HASHBYTES(''SHA2_256'', CONCAT_WS(''|''' + <HashValue> +')): Calculate field to hash row by concatenating all columns

DECLARE @tablename VARCHAR(50) = 'PersonSlim'
DECLARE @hash VARCHAR(MAX) = '';

SELECT @hash = @hash + ',ISNULL(CAST(' + [ColumnName] + ' AS VARCHAR), '''')' + CHAR(13)+CHAR(10)
  FROM [dbo].[TableSchema]
 WHERE TableName = @tablename
 ORDER BY [id]
 PRINT @hash -- Debug

Point1) @hash = @hash + [<string | fields>]

I used the same concept which is from Point1 in Part1 - <columns>.

Point2) I used CONCAT_WS function to concatenate fields with pipe(“|”) delimiter. (Reference Post: SQL Server: CONCAT, CONCAT_WS - Concatenating fields to hash row)

Execute) The above query will output the string below:

PART3 - [ <Primary Key> ] ASC: Set Primary Key

DECLARE @tablename VARCHAR(50) = 'PersonSlim'
DECLARE @pk   VARCHAR(32);

SELECT @pk = [ColumnName] 
  FROM [dbo].[TableSchema]
 WHERE [TableName] = @tablename
   AND [IsPrimaryKey] = 1

PRINT @pk -- Debug

There are no specific points here

Execute) The above query will output the below string:

I combined the 3 separate parts above and turned it into one stored procedure (see below).

CREATE OR ALTER PROCEDURE [dbo].[usp_CreateTable]
	@tablename VARCHAR(50)
AS
BEGIN
	DECLARE @cols VARCHAR(MAX) = '';
	DECLARE @hash VARCHAR(MAX) = '';
	DECLARE @pk   VARCHAR(32);

	SELECT	@hash = @hash + ',ISNULL(CAST(' + [ColumnName] + ' AS VARCHAR), '''')' + CHAR(13)+CHAR(10)
	FROM	[dbo].[TableSchema]
	WHERE	TableName = @tablename
	ORDER BY [id]

	SELECT	@cols = @cols + '[' + [ColumnName] + ']' + ' ' + '[' + [DataType] + ']' 
				+ IIF([DataType] = 'varchar', '('+ CAST([DataLength] AS VARCHAR) +')',  '') + ' ' 
				+ IIF([Nullable] = 1, 'NULL', 'NOT NULL')
				+ ',' + CHAR(13)+CHAR(10)
	FROM	[dbo].[TableSchema]
	WHERE	TableName = @tablename
	ORDER BY [id]

	SELECT	@pk = [ColumnName] 
	FROM	[dbo].[TableSchema]
	WHERE	[TableName] = @tablename
	AND		[IsPrimaryKey] = 1

    DECLARE @Sql VARCHAR(MAX) = '
    IF OBJECT_ID(''[dbo].['+@tablename+']'') IS NULL
    BEGIN
        CREATE TABLE [dbo].['+@tablename+']
        (
			'+@cols+'
			[_HashValue] AS HASHBYTES(''SHA2_256'', CONCAT_WS(''|''' + @hash +')),
		PRIMARY KEY CLUSTERED 
		(
			['+@pk+'] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
		) ON [PRIMARY]
    END
    '
    EXEC(@Sql)
END

EXEC dbo.usp_CreateTable @tablename = 'PersonSlim'

The stored procedure will generate the “PersonSlim” table like the screenshot below:

I hope this content helped you to understand how to use Dynamic SQL. There is still much debate about Dynamic SQL. The flexibility of Dynamic SQL makes it a very advantageous and powerful tool for SQL developers. As mentioned  before, this post highlights what is Dynamic SQL and demonstrates how to use it in a simple case. (* If you are interested in the Dynamic SQL, I recommend researching “Static SQL Vs. Dynamic SQL”)

# Reference

• EXECUTE()
• sp_executesql