Create Generic Template to Create Store Procedure

Posted by Lakhan Pal Garg on Monday, September 10, 2012
Labels: / Comments: (0)
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)
       DECLARE @IsIdentityExist Int, @paramList VARCHAR(4000)
       DECLARE @ScriptTable Table(ID INT IDENTITY(1,1)
       DECLARE 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