SQL Server – Management Studio Tip: Change Default templates for Stored Procedures

Hi Friends,

So here is another trick in SQL Server Management Studio. Many of you might create a New Stored Procedure using the New Menu Item as shown below:

1_SQL_Server_Management_Studio_Tip_Change_Defaul_templates_for_Stored_Procedures

your needs. Ever wondered if you can customize this default template to get your own template every time you create a new Stored Procedure? So here is the trick:

Modify the file ‘Create Stored Procedure (New Menu).sql’ which is located in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure. (For 64 bit installation, the path is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure).

‘Create Stored Procedure (New Menu).sql’ is the file that has the default code that comes up every time you create a new Proc. You can modify it the way you want. For example, by default, it has to input parameters, param1 & param 2; and you generally work with more parameters; so go ahead and edit the template and add another parameter or any other customization that you feel like. See the Original template code and the modified one below:

Original:

   
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN

After Modification:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>,
<@Param3, sysname, @p3> <Datatype_For_Param3, , int> = <Default_Value_For_Param3, , 0>
AS
BEGIN

And there are many other templates there which you can customize:

2_SQL_Server_Management_Studio_Tip_Change_Defaul_templates_for_Stored_Procedures

Happy tricking SQL Server 😉

Sarab, Sachin, AmitK and many other contributors and posting very valuable content on the site. In case you like our content, please leave a word of apprecaition by posting some comments. This definitely motivates us to bring more learning content to you.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “SQL Server – Management Studio Tip: Change Default templates for Stored Procedures”

  1. For SQL 2014 its C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\

  2. For 2016(64bit) – had to add my account to folder permissions to replace file.
    C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Stored Procedure

    Interesting that editing what appears to be the same template from Template Explorer does not update the correct file. Thanks for this post, very helpful
    Sorry, last comment didn’t copy new path.

Leave a Reply

Your email address will not be published.