A Stored Procedure to Generate a Stored Procedure Template

SQL Server has a default stored procedure template. However, I need my database stored procedures to have the author's name and the procedure created date, so I wrote such a stored procedure to generate a template with those information.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USP_StoredProcedureTemplate]
    @p1 varchar(10) = NULL,
    @p2 varchar(10) = NULL
AS
BEGIN

 DECLARE @p11 char(2), @p21 char(2)
 SET @p11 = ‘p1′
 SET @p21 = ‘p2′
 IF (@p1 IS NOT NULL)
  SET @p11 = @p1
 IF (@p2 IS NOT NULL)
  SET @p21 = @p2
 
 PRINT ‘– Stored Procedure: <procedure_name,,>’
 PRINT CHAR(13)
 PRINT ‘– =============================================’
 PRINT ‘– Author: ‘ + SUSER_SNAME()
 PRINT ‘– Create date: ‘ + CAST(GETDATE() AS varchar)
 PRINT ‘– Description: <description,,>’
 PRINT ‘– =============================================’

 PRINT CHAR(10)
 PRINT ‘SET ANSI_NULLS ON’
 PRINT ‘GO’
 PRINT ‘SET QUOTED_IDENTIFIER ON’
 PRINT ‘GO ‘

 PRINT ‘CREATE PROCEDURE <procedure_name,>’
 PRINT ‘  <@Param1, sysname, @’ + @p11 + ‘> <datatype_for_param1,> = <default_value_for_param1,>,’
 PRINT ‘  <@Param2, sysname, @’ + @p21 + ‘> <datatype_for_param2,> = <default_value_for_param2,>’
 PRINT ‘AS’

 PRINT ‘BEGIN’
 PRINT ‘SET NOCOUNT ON;’

 PRINT CHAR(10)
 PRINT ‘  – Insert your SQL Statements here’

 PRINT CHAR(10)
 PRINT ‘END’
 PRINT ‘GO ‘

END