Auto generated SP with Parameters

 

When we are thinking about a procedure to insert data in a specified table, we must use all the columns name of the table as parameter to supply the value on the procedure.

Sometimes the table have a huge number of columns and the developer are bored to make parameters in all the columns and there data types and whether the columns is taking NULL or NOT NULL values.

In this article I am providing a simple stored procedure to generate a frame of stored procedure. My stored procedure takes two arguments. One is the SP name and another is the table name where we want to insert the data or make any operations.

The main thing is that it generates the parameters automatically from supplied table objects.

The Stored procedure is ready to use and you can enjoy it by just copy and paste.

IF EXISTS (SELECT *

           FROM   sysobjects

           WHERE  type = 'P'

                  AND name = 'up_RUN_SP_FRAME_CREATOR')

      BEGIN

            DROP  PROCEDURE  up_RUN_SP_FRAME_CREATOR

      END

GO

 

CREATE Procedure [dbo].[up_RUN_SP_FRAME_CREATOR]

      (

         @p_SPName  VARCHAR(30)  =  NULL,

         @p_TBLName VARCHAR(MAX) =  NULL

      )

AS

      DECLARE @sqlString       AS NVARCHAR(MAX)

      CREATE TABLE #Tbl

      (TblTxt VARCHAR(MAX))

BEGIN

        SET NOCOUNT ON;

       

        IF ISNULL(@p_SPName,'')='' OR ISNULL(@p_TBLName,'')=''

           BEGIN

               PRINT ('Please supply All the Parameters Value')

               GOTO WayOut

           END

       

        INSERT INTO #Tbl

        SELECT 'CREATE PROCEDURE '+ @p_SPName

        UNION ALL

        SELECT '('

        UNION ALL

        SELECT '@p_' + column_name + REPLICATE(' ', 40-LEN(column_name))

                       + data_type   + CASE WHEN data_type LIKE '%char' OR data_type LIKE '%binary'

                       THEN ' (' + CASE WHEN CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) = '-1'

                       THEN 'max'

                        ELSE CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH)

                        END + ')'

                        WHEN data_type IN ('decimal', 'numeric')

                        THEN ' (' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ', '

                       + CONVERT(VARCHAR, NUMERIC_SCALE) + ')'

                      WHEN data_type = 'float' THEN '(' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ')'

                      ELSE ''

                      END

                      + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT' ELSE '' END + ' NULL'

        FROM   information_schema.columns

       WHERE  table_name = @P_TBLName

       UNION ALL

       SELECT ') AS WITH ENCRYPTION'

       UNION ALL

      SELECT 'DECLARE @....    <dataTypes>'

      UNION ALL

      SELECT 'DECLARE @....    <dataTypes>'

      UNION ALL

      SELECT 'DECLARE @....    <dataTypes>'

      UNION ALL

      SELECT 'BEGIN'

      UNION ALL

      SELECT '<Body.....>'

      UNION ALL

      SELECT 'END'

      SELECT * FROM #Tbl

WayOut:

END

 

GO

 

 

To run this stored procedure

 

EXEC up_RUN_SP_FRAME_CREATOR

     @p_SPName='my_Sp',

     @P_TBLName='my_Table'

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS