T-SQL to Generate T-SQL

Question

T-SQL to Generate T-SQL

GaryMcAllister

Mr or Mrs. 500

Points: 521
More actions
April 13, 2006 at 3:40 am

#167811

Ok, i'm a developer and I got feb up writing the same boring crap day in and day out. So ive written a set of tools to help my life.
Here is the T-SQL code generator I wrote which generate T-SQL stored procedures for me.
It only produces the basics but its enough to save me plenty of head-aches.
/******************************************
Stored Procedure Generation Code.
Written By Gary McAllister 13th April 2006
-------------------------------------------
Email:
garymcallister[_at_]hotmail.com,
garymcallister[_at_]kingsch.nhs.uk
-------------------------------------------
Description:
T-SQL used to generate Stored Procedures
Based on table definitions.
*******************************************/
DECLARE
@SqlCommand AS NVARCHAR(10),
@TableName
SYSNAME,
@ProcedureName
NVARCHAR(255),
@IncludePKParam
BIT
SET
@TableName = '' --The name of the Sql server table
SET
@ProcedureName = '' --The name of the new Stored Procedure.
SET
@SqlCommand = '' --The name of the Sql Command (Insert, Update, Delete, Select)
SET
@IncludePKParam = 0 --Whether to include primary key fields as parameter, only really applies to INSERT Statements
IF
@SqlCommand NOT IN ('INSERT','UPDATE', 'DELETE', 'SELECT')
BEGIN
RAISERROR('Invalid SQL Command provided, please use INSERT, UPDATE, DELETE, SELECT for @SqlCommand parameter.',16,1)
RETURN
END
IF
@ProcedureName = ''
BEGIN
RAISERROR('Please provide a procedure name.',16,1)
RETURN
END
IF
@TableName = ''
BEGIN
RAISERROR('Please provide a table name.',16,1)
RETURN
END
IF
NOT EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name = @TableName)
BEGIN
RAISERROR('Table does not exist in current database.',16,1)
RETURN
END
DECLARE
@sSql AS NVARCHAR(4000)
DECLARE
@sSqlQueryStart AS NVARCHAR(4000)
DECLARE
@sSqlQueryEnd AS NVARCHAR(4000)
DECLARE
@sSqlParams AS NVARCHAR(4000)
DECLARE
@PKWHERE AS NVARCHAR(4000)
SELECT
@PKWHERE = ''
SELECT
@sSqlQueryStart = @SqlCommand
SELECT
@sSqlQueryEnd = ''
SELECT
@sSqlParams = ''
SELECT
@sSql = ''
DECLARE
@ColumnName NVARCHAR(1024), @DataType NVARCHAR(255), @CharMaxLen INT
DECLARE
cColumns CURSOR FOR SELECT Column_Name, Data_Type, Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName ORDER BY ORDINAL_POSITION
OPEN
cColumns
FETCH
NEXT FROM cColumns INTO @ColumnName, @DataType, @CharMaxLen
SELECT
@sSql = @sSql + 'CREATE PROCEDURE ' + @ProcedureName + char(13) + '(' + char(13)
IF
@SqlCommand = 'INSERT'
SELECT @sSqlQueryStart = @sSqlQueryStart + ' INTO ' + @TableName + char(13) + '('
IF
@SqlCommand = 'UPDATE'
SELECT @sSqlQueryStart = @sSqlQueryStart + ' ' + @TableName + char(13) + 'SET' + char(13)
IF
@SqlCommand = 'SELECT'
SELECT @sSqlQueryStart = 'SELECT' + char(13) + char(9)
IF
@SqlCommand <> 'DELETE'
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SqlCommand IN ('INSERT','UPDATE')
BEGIN
DECLARE @ccolMaximum AS NVARCHAR(255)
IF @DataType NOT IN ('TEXT','NTEXT')
BEGIN
SELECT @ccolMaximum = '(' + CAST(@CharMaxLen AS NVARCHAR(50)) + ')'
IF NOT @ccolMaximum IS NULL
SELECT @ccolMaximum = REPLACE(@ccolMaximum,'-1','MAX')
END
IF @IncludePKParam = 0
BEGIN
IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
SELECT @sSqlParams = @sSqlParams + '@' + @ColumnName + ' ' + @DataType + ISNULL(@ccolMaximum,'') + ',' + char(13)
END
ELSE
SELECT @sSqlParams = @sSqlParams + '@' + @ColumnName + ' ' + @DataType + ISNULL(@ccolMaximum,'') + ',' + char(13)
END
SELECT @sSqlQueryStart = @sSqlQueryStart +
CASE WHEN @SqlCommand IN ('INSERT','SELECT')
THEN
CASE WHEN @IncludePKParam = 1
THEN
@ColumnName
+ ','
ELSE
CASE WHEN NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
THEN
@ColumnName
+ ','
ELSE
''
END
END
ELSE
CASE WHEN @SqlCommand = 'UPDATE' THEN
/*CASE WHEN @IncludePKParam = 1
THEN
char(9) + ISNULL(@ColumnName,'') + ' = @' + ISNULL(@ColumnName,'') + ',' + char(13)
ELSE
CASE WHEN NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
THEN*/
char(9) + ISNULL(@ColumnName,'') + ' = @' + ISNULL(@ColumnName,'') + ',' + char(13)
/*ELSE
''
END
END */
ELSE
''
END
END
IF @SqlCommand = 'INSERT'
BEGIN
IF @IncludePKParam = 0
BEGIN
IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
SELECT @sSqlQueryEnd = @sSqlQueryEnd + '@' + @ColumnName + ','
END
ELSE
SELECT @sSqlQueryEnd = @sSqlQueryEnd + '@' + @ColumnName + ','
END
FETCH NEXT FROM cColumns INTO @ColumnName, @DataType, @CharMaxLen
END
END
IF
@SqlCommand <> 'INSERT'
BEGIN
IF @SqlCommand <> 'UPDATE'
SELECT @sSqlParams = ''
DECLARE cWhere CURSOR FOR SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName
DECLARE @pkColumnName NVARCHAR(1024), @pkColumnPosition INT
OPEN cWhere
FETCH NEXT FROM cWhere INTO @pkColumnName, @pkColumnPosition
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @colDataType NVARCHAR(255), @colCharMaxLen INT
SELECT @colDataType=Data_Type,@colCharMaxLen=Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName AND ORDINAL_POSITION = @pkColumnPosition AND COLUMN_NAME = @pkColumnName
IF @SqlCommand <> 'UPDATE'
BEGIN
IF @DataType NOT IN ('TEXT','NTEXT')
BEGIN
DECLARE @colMaximum AS NVARCHAR(255)
SELECT @colMaximum = '(' + CAST(@colCharMaxLen AS NVARCHAR(50)) + ')'
END
SELECT @sSqlParams = @sSqlParams + '@' + ISNULL(@pkColumnName,'') + ' ' + ISNULL(@colDataType,'') + ISNULL(@colMaximum,'') + ',' + char(13)
END
SELECT @PKWHERE = @PKWHERE + @pkColumnName + ' = @' + @pkColumnName + ' AND ' + char(13)
FETCH NEXT FROM cWhere INTO @pkColumnName, @pkColumnPosition
END
SELECT @PKWHERE = SUBSTRING(@PKWHERE,1,LEN(@PKWHERE) -6)
CLOSE cWhere
DEALLOCATE cWhere
END
IF
@SqlCommand = 'DELETE'
SELECT @sSqlQueryStart = 'DELETE FROM ' + @TableName + ' WHERE ' + @PKWHERE
IF
@SqlCommand = 'INSERT'
BEGIN
SELECT @sSqlQueryStart = SUBSTRING(@sSqlQueryStart,1,LEN(@sSqlQueryStart)-1) + ')' + char(13) + 'VALUES' + char(13) + '('
SELECT @sSqlQueryEnd = SUBSTRING(@sSqlQueryEnd,1,LEN(@sSqlQueryEnd)-1) + ')'
END
IF
@SqlCommand = 'UPDATE'
BEGIN
SELECT @sSqlQueryStart = SUBSTRING(@sSqlQueryStart,1,LEN(@sSqlQueryStart)-2) + char(13) + 'WHERE' + char(13) + char(9) + @PKWHERE
END
IF
@SqlCommand = 'SELECT'
BEGIN
SELECT @sSqlQueryStart = SUBSTRING(@sSqlQueryStart,1,LEN(@sSqlQueryStart)-1) + char(13) + 'FROM' + char(13) + char(9) + @TableName + char(13) + 'WHERE' + char(13) + char(9) + @PKWHERE
END
IF
LEN(@sSqlParams) > 2
SELECT @sSqlParams = SUBSTRING(@sSqlParams,1,LEN(@sSqlParams) -2) + char(13) + ')' + char(13) + 'AS' + char(13)
ELSE
SELECT @sSqlParams = @sSqlParams + char(13) + ')' + char(13) + 'AS' + char(13)
CLOSE
cColumns
DEALLOCATE
cColumns
PRINT
ISNULL(@sSql,'') + ISNULL(@sSqlParams,'') + char(13) + ISNULL(@sSqlQueryStart,'') + ISNULL(@sSqlQueryEnd,'') + char(13) + char(13)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 1

Great work, Gary! If you wanted to use this as part of a GUI so that the GUI would run this procedure, then execute the generated SQL to actually CREATE the new procedure, how would you execute the SQL if it had more than 8,000 characters?

1980gamer SSC Enthusiast Points: 139 More actions · Answer 2

This is FANTASTIC! Great Job Gary.

I have the same question as Carlos, 8K limit!

I have one denormilized table that this doesn't really work on.

I changed the data type of the variables from NVARCHAR to VARCHAR, That gave me most of the prcedure.

This is still great at getting the SP started

I cannot believe I didn't think of this before....

Once again, great job! I may try a few things to get past 8K.

If I figure a way out, I'll reply again.

Carlos, I don't know how big your table is, but If you don't need NVARCHAR, you can double your space with VARCHAR.

Gene

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 3

In SQL 2005 you don't have those issues because you can use NVARCHAR(MAX).

Basically the proceedure spits out in 4 parts so you could write a front end which takes the 4 parts puts them back together using output parameters.

SQL Server 2000 is a pain in the backside when it comes to string manipulation because of the data-type limitations.

Have fun with it though.. The limits are endless.