changing the default stored procedure template

Ingeniously simple database development in Visual Studio

changing the default stored procedure template

Postby irishmaninada » Mon Jul 23, 2012 2:43 pm

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.
irishmaninada
 
Posts: 8
Joined: Mon Jul 23, 2012 2:41 pm

Postby David Atkinson » Mon Jul 23, 2012 3:15 pm

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

my template

Postby irishmaninada » Tue Jul 24, 2012 2:35 pm

it is pretty basic, nothing too drastic, but just something we have used here at where I work.

Code: Select all
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.
irishmaninada
 
Posts: 8
Joined: Mon Jul 23, 2012 2:41 pm

Postby David Atkinson » Tue Jul 24, 2012 6:06 pm

Thanks for sharing this.

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

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby irishmaninada » Tue Jul 24, 2012 6:37 pm

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.
irishmaninada
 
Posts: 8
Joined: Mon Jul 23, 2012 2:41 pm


Return to SQL Connect

Who is online

Users browsing this forum: No registered users and 0 guests