Create Generic Template to Create Store Procedure

This store procedure will be used to create a generic SP for insert, update, delete, select, select all statement.

This will create a store procedure script for the given table and SPType (Insert, Update, Select, SelectAll, Delete).

Copy the following Store Procedure:

/*
Created By: Lakhan Pal
Created On: 07/02/2012
Purpose: To Create Store Procedure Template for Given Table for
Insert, Update, Select, SelectAll, And Delete Script

User Just need to Pass Following Arguments
(1) @TableName VARCHAR(100)
(2) @AuthorName VARCHAR(100)
(3) @SPType VARCHAR(15) (Insert, Update, Select, SelectAll, Delete)
(4) @Comment VARCHAR(500) [Optional]

Execution Statement:
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Insert'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Update'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Select'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'SelectAll'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Delete'
*/
CREATE PROCEDURE uspCreateGenericTemplateForSP
(
@TableName VARCHAR(100),
@AuthorName VARCHAR(100),
@SPType VARCHAR(15),
@Comment VARCHAR(500)=NULL
)
AS
BEGIN
DECLARE @MaxID INT, @ColList VARCHAR(4000), @paramList VARCHAR(4000)
DECLARE @IsIdentityExist Int
DECLARE @ScriptTable Table(ID INT IDENTITY(1,1),Script VARCHAR(5000), RecordType Char(1))

SET @IsIdentityExist =0
SET @ColList =''
SET @paramList =''

IF (@Comment IS NULL)
SET @Comment =' To Create script for ' + @SPType
+ ' Record into Table ' + @TableName

/* To Check the Identity Column */
SELECT @IsIdentityExist =COUNT(Name) FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

/* Comment Section - Start Here */
INSERT INTO @ScriptTable(Script) VALUES('/*')
INSERT INTO @ScriptTable(Script)
VALUES('Created By: ' + @AuthorName)
INSERT INTO @ScriptTable(Script)
VALUES('Created On: ' + CONVERT(VARCHAR(10), GETDATE(), 103))
INSERT INTO @ScriptTable(Script)
VALUES('Purpose: ' + @Comment)
INSERT INTO @ScriptTable(Script) VALUES('*/')
/* Comment Section - End Here */

INSERT INTO @ScriptTable(Script)
VALUES('CREATE PROCEDURE usp' +@SPType + '_'+ @TableName )
INSERT INTO @ScriptTable(Script) VALUES('(')

/* If Identity Column Exists */
IF (@IsIdentityExist>0)
BEGIN
INSERT INTO @ScriptTable(Script, RecordType)
(
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =1)
END

IF(@SPType ='Insert' OR @SPType ='Update')
BEGIN
/* Parameter Section - Start Here */
INSERT INTO @ScriptTable(Script, RecordType)
(
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0 AND
T.System_Type_ID NOT IN (173, 175, 239, 231,165, 167,106, 108 )
UNION
SELECT ' @' + C.Name + ' ' + T.Name + '(' +
CONVERT(VARCHAR(10),C.MAX_Length)
+ ', '+ CONVERT(VARCHAR(10),C.Precision) + ')' + ',', 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0
AND T.System_Type_ID IN (106, 108)
UNION
SELECT ' @' + C.Name + ' ' + T.Name + '(' +
CONVERT(VARCHAR(10),C.MAX_Length) + ')' + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0
AND T.System_Type_ID IN (173, 175, 239, 231,165, 167 )
)
/* Parameter Section - End Here */
END

SELECT @MaxID = MAX(ID) FROM @ScriptTable WHERE RecordType='P'
UPDATE @ScriptTable SET Script =
SUBSTRING(Script,0,Len(Script)) WHERE ID=@MaxID

INSERT INTO @ScriptTable(Script) VALUES(')')
INSERT INTO @ScriptTable(Script) VALUES('AS')
INSERT INTO @ScriptTable(Script) VALUES(' BEGIN')

/* Query Section - Start Here */
IF(@SPType = 'Insert')
BEGIN
SELECT @ColList = @ColList + name +',',
@paramList = @paramList + '@' + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =0

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
SET @paramList = SUBSTRING(@paramList,0,Len(@paramList))

INSERT INTO @ScriptTable(Script)
VALUES(' INSERT INTO ' + @TableName )
INSERT INTO @ScriptTable(Script)
VALUES(' (' + @ColList + ')')
INSERT INTO @ScriptTable(Script)
VALUES(' VALUES (' + @paramList + ')')
END
ELSE IF (@SPType ='Update')
BEGIN
SELECT @ColList = @ColList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =0

SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' UPDATE ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' SET ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
ELSE IF(@SPType= 'Select')
BEGIN
SELECT @ColList = @ColList + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName)AND is_identity =0

SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' SELECT ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' FROM ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
ELSE IF(@SPType= 'SelectAll')
BEGIN
SELECT @ColList = @ColList + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName)

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))

INSERT INTO @ScriptTable(Script)
VALUES(' SELECT ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' FROM ' + @TableName)
END
ELSE IF(@SPType ='Delete')
BEGIN
SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' DELETE FROM ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
/* Query Section - End Here */

INSERT INTO @ScriptTable(Script) VALUES(' END')

SELECT Script FROM @ScriptTable
END