Competition: What’s your favorite Redgate tool? Enter now.

changing the default stored procedure template

irishmaninadairishmaninada Posts: 11
edited July 24, 2012 1:37PM in SQL Connect
Is there a way to change the default stored procedure template within a sql connect project, I want to set it up so that it uses my default template for stored procedure.

Comments

  • That's a good idea. In the meantime you can use a SQL Prompt snippet. There is a built-in snippet, 'cp' that you can use.

    You can modify this or add your own new snippet in the Snippet Manager from the SQL Prompt menu.

    Out of interest, what does your template look like?

    Kind regards,

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
  • it is pretty basic, nothing too drastic, but just something we have used here at where I work.
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROCNAME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    	BEGIN
    		PRINT 'Dropping Procedure dbo.PROCNAME'
    		drop procedure [dbo].[PROCNAME]
    	END
    
    GO
    
    PRINT 'Creating Procedure Stored_Procedure_Name'
    GO
    /*
    	Name			:	PROCNAME
    	Purpose			:	WHAT THE STORED PROCEDURE DOES
    
    	Author			:	JohnD (COMPANY)
    	Date			:	DATE CREATED
    	Parameter(s)	:	PARAMETERS
    						
    	Note(s)			:	
    */
    
    
    CREATE PROCEDURE dbo.PROCNAME  (
    	-- ParameterList if any, if none the remove the ()
    ) AS
    
    
    	-- Set nocount on
    	SET NOCOUNT ON
    	
    	-- Query to be run
    	
    	-- SEt nocount off
    	SET NOCOUNT OFF
    	
    	
    GO
    
    GRANT EXEC ON dbo.PROCNAME TO db_user_account
    
    GO
    

    PROCNAME is replaced with the name of the procedure you wish to create, and covers all the basis that we use in creating a new proc.
  • Thanks for sharing this.

    This looks ideal snippet material. Did you try creating one?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • now that is cool, I made the change in sql prompt (actually added a new one just on the safe side) and it worked like a charm.
Sign In or Register to comment.