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

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)