AUTOGENERATE INSERT UPDATE SELECT STATEMENTS
PLEASE NOTE: THIS IS THE OLD VERSION
THE NEW VERSION IS HERE
All you have to do is change the @tablename and @authorname parameters and use the resulting code.
i have included the primary key in the insert and update statements even though it is not valid.
the code generated is meant to be used as a template to create your actual production SP's
please refer to http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/ for the updated version
have fun
Chris Morton cbmorton [AT{ [gmail( . dot . } com ))]
--please refer to http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/
--for the updated version.
DECLARE @TableName VARCHAR(50)
SET @TableName = 'Campaign'
DECLARE @AuthorName VARCHAR(50)
SET @AuthorName = 'Chris Morton'
DECLARE @CreatedDate VARCHAR(50)
SET @CreatedDate = CONVERT(VARCHAR(50), GETDATE(), 111)
DECLARE @SPName VARCHAR(100)
DECLARE @SPDescription VARCHAR(1000)
SET @SPDescription = '-- ============================================='
+ CHAR(10) + CHAR(13) + '-- AUTHOR: ' + @AuthorName + CHAR(10) + CHAR(13)
+ '-- CREATED DATE: ' + @CreatedDate + CHAR(10) + CHAR(13)
+ '-- =============================================' + CHAR(10) + CHAR(13)
DECLARE @Statement VARCHAR(4000)
DECLARE @SPText VARCHAR(8000)
DECLARE @ParameterName VARCHAR(50)
DECLARE @ParameterDataType VARCHAR(50)
DECLARE @ParameterDataTypeLength VARCHAR(4)
DECLARE @ParameterList VARCHAR(2000)
DECLARE @SelectStatement VARCHAR(4000)
SET @SelectStatement = 'SELECT'
DECLARE @InsertStatement VARCHAR(4000)
SET @InsertStatement = 'INSERT INTO'
DECLARE @InsertStatementValues VARCHAR(2000)
DECLARE @TableMetaData AS TABLE
(
ID INT IDENTITY(1, 1),
ColumnName VARCHAR(50) NOT NULL,
DataType VARCHAR(50) NOT NULL,
Length VARCHAR(4) NOT NULL
)
INSERT INTO @TableMetaData
(
ColumnName,
DataType,
Length
)
SELECT syscolumns.name AS ColumnName,
systypes.name AS Datatype,
syscolumns.length AS Length
FROM sysobjects,
syscolumns,
systypes
WHERE sysobjects.id = syscolumns.id
AND syscolumns.xtype = systypes.xtype
AND sysobjects.name = @TableName
DECLARE @NumColumns INT
SET @NumColumns = ( SELECT COUNT(1)
FROM @TableMetaData
)
DECLARE @ColumnName VARCHAR(50)
DECLARE @Counter INT
SET @Counter = 1
--select statement
SET @SPName = 'CREATE PROCEDURE select' + @TableName + CHAR(10) + CHAR(13)
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
IF @Counter = @NumColumns
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10) + CHAR(13)
+ ' [' + @ColumnName + ']' + +CHAR(10) + CHAR(13)
+ 'FROM ' + CHAR(10) + CHAR(13) + @TableName
END
ELSE
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10) + CHAR(13)
+ ' [' + @ColumnName + '],'
END
SET @Counter = @Counter + 1
END
SET @Statement = @SelectStatement
SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,
CHAR(10) + CHAR(13)) + 'AS'
+ CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement
+ CHAR(10) + CHAR(13) + 'END'
SELECT @SPText AS SelectStatement
--INSERT STATEMENT
SET @SPName = 'CREATE PROCEDURE insert' + @TableName + CHAR(10) + CHAR(13)
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterDataType = ( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar'
THEN '(' + Length + ')'
WHEN 'char'
THEN '(' + Length + ')'
WHEN 'varbinary'
THEN '(' + Length + ')'
WHEN 'binary'
THEN '(' + Length + ')'
WHEN 'nchar'
THEN '(' + Length + ')'
WHEN 'nvarchar'
THEN '(' + Length + ')'
WHEN 'decimal'
THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterName = '@' + @ColumnName
IF @Counter = 1
BEGIN
SET @InsertStatement = @InsertStatement + ' ' + @TableName
+ CHAR(10) + CHAR(13) + ' ([' + @ColumnName + '],'
+ CHAR(10) + CHAR(13)
SET @InsertStatementValues = @ParameterName + ',' + CHAR(10)
+ CHAR(13)
SET @ParameterList = @ParameterName + ' ' + @ParameterDataType
+ ', ' + CHAR(10) + CHAR(13)
END
IF @Counter > 1
AND @Counter < @NumColumns - 1
BEGIN
SET @InsertStatement = @InsertStatement + ' [' + @ColumnName
+ '],' + CHAR(10) + CHAR(13)
SET @InsertStatementValues = @InsertStatementValues + ' '
+ @ParameterName + ',' + CHAR(10) + CHAR(13)
SET @ParameterList = @ParameterList + @ParameterName + ' '
+ @ParameterDataType + ',' + CHAR(10) + CHAR(13)
END
IF @Counter = @NumColumns
BEGIN
SET @InsertStatement = @InsertStatement + '[' + @ColumnName
+ '])' + CHAR(10) + CHAR(13) + 'VALUES' + CHAR(10)
+ CHAR(13) + '('
SET @InsertStatementValues = @InsertStatementValues + ' '
+ @ParameterName + ')'
SET @ParameterList = @ParameterList + @ParameterName + ' '
+ @ParameterDataType
END
SET @Counter = @Counter + 1
END
SET @Statement = @InsertStatement + @InsertStatementValues
--construct text
SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,
CHAR(10) + CHAR(13)) + 'AS'
+ CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement
+ CHAR(10) + CHAR(13) + 'END'
SELECT @SPText AS insertStatement
--update statement
DECLARE @ColumnParameter VARCHAR(4000)
SET @ColumnParameter = ''
SET @SPName = 'CREATE PROCEDURE update' + @TableName + CHAR(10) + CHAR(13)
DECLARE @UpdateStatement VARCHAR(8000)
SET @UpdateStatement = 'UPDATE ' + @TableName + CHAR(10) + CHAR(13) + 'SET '
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterName = '@' + @ColumnName
SET @ParameterDataType = ( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar'
THEN '(' + Length + ')'
WHEN 'char'
THEN '(' + Length + ')'
WHEN 'varbinary'
THEN '(' + Length + ')'
WHEN 'binary'
THEN '(' + Length + ')'
WHEN 'nchar'
THEN '(' + Length + ')'
WHEN 'nvarchar'
THEN '(' + Length + ')'
WHEN 'decimal'
THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
)
IF @Counter = 1
BEGIN
SET @ParameterList = @ParameterName + ' ' + @ParameterDataType
+ ', ' + CHAR(10) + CHAR(13)
END
IF @Counter = @NumColumns
BEGIN
SET @ColumnParameter = @ColumnParameter + @ColumnName + ' = '
+ @ParameterName + CHAR(10) + CHAR(13)
SET @ParameterList = @ParameterList + @ParameterName + ' '
+ @ParameterDataType + CHAR(10) + CHAR(13)
END
IF @Counter > 1
AND @Counter < @NumColumns - 1
BEGIN
SET @ColumnParameter = @ColumnParameter + @ColumnName + ' = '
+ @ParameterName + ',' + CHAR(10) + CHAR(13)
SET @ParameterList = @ParameterList + @ParameterName + ' '
+ @ParameterDataType + ',' + CHAR(10) + CHAR(13)
END
SET @Counter = @Counter + 1
END
SET @Statement = @UpdateStatement + @ColumnParameter
SET @SPText = @SPDescription + @SPName + ISNULL(@ParameterList,
CHAR(10) + CHAR(13)) + 'AS'
+ CHAR(10) + CHAR(13) + 'BEGIN' + CHAR(10) + CHAR(13) + @Statement
+ CHAR(10) + CHAR(13) + 'END'
SELECT @SPText AS insertStatement 
