changing the default stored procedure template

Comments

• David Atkinson Redgate › Posts: 1,458 Rose Gold 2

  July 23, 2012 10:15AM

  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

  0 · Share on Twitter
• irishmaninada Posts: 11

  July 24, 2012 9:35AM

  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.

  0 · Share on Twitter
• David Atkinson Redgate › Posts: 1,458 Rose Gold 2

  July 24, 2012 1:06PM

  Thanks for sharing this.
  
  This looks ideal snippet material. Did you try creating one?
  
  David

  David Atkinson
  Product Manager
  Redgate Software

  0 · Share on Twitter
• irishmaninada Posts: 11

  July 24, 2012 1:37PM

  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.

  0 · Share on Twitter