Click here to Skip to main content
Click here to Skip to main content

Tagged as

A Stored Procedure to Generate a Stored Procedure Template

, 30 Aug 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

scott_liu
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
GeneralReason for my vote of 5 Great tip for helping create a stand... Pinmemberwschalk21-Sep-11 7:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 30 Aug 2011
Article Copyright 2011 by scott_liu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid