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:

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:

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.