Red Gate forums :: View topic - changing the default stored procedure template
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Connect
SQL Connect forum

changing the default stored procedure template

Search in SQL Connect forum
Post new topic   Reply to topic
Jump to:  
Author Message
irishmaninada



Joined: 23 Jul 2012
Posts: 8

PostPosted: Mon Jul 23, 2012 2:43 pm    Post subject: changing the default stored procedure template Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1122
Location: Twitter: @dtabase

PostPosted: Mon Jul 23, 2012 3:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
irishmaninada



Joined: 23 Jul 2012
Posts: 8

PostPosted: Tue Jul 24, 2012 2:35 pm    Post subject: my template Reply with quote

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

Code:

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1122
Location: Twitter: @dtabase

PostPosted: Tue Jul 24, 2012 6:06 pm    Post subject: Reply with quote

Thanks for sharing this.

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

David
Back to top
View user's profile Send private message Send e-mail
irishmaninada



Joined: 23 Jul 2012
Posts: 8

PostPosted: Tue Jul 24, 2012 6:37 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group