none
How to modify existing stored procedure in sql server 2008

    Question

  • Hi

          I would like to modify existing stored procedure template which comes when i do right click on stored procedure in object explorer window. How can i do this ?

           Following is template which comes in query window when new stored procedure calls.

         

    -- ================================================

    -- Template generated from Template Explorer using:

    -- Create Procedure (New Menu).SQL

    --

    -- Use the Specify Values for Template Parameters

    -- command (Ctrl-Shift-M) to fill in the parameter

    -- values below.

    --

    -- This block of comments will not be included in

    -- the definition of the procedure.

    -- ================================================

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    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

     

    -- SET NOCOUNT ON added to prevent extra result sets from

     

    -- interfering with SELECT statements.

     

    SET NOCOUNT ON;

     

    -- Insert statements for procedure here

     

    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

    END

    GO


    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    • Moved by Tom PhillipsModerator Wednesday, June 02, 2010 2:11 PM TSQL Question (From:SQL Server Database Engine)
    Wednesday, June 02, 2010 7:33 AM

Answers

  • Hi Raj,

    Please navigate to the following location, edit the file called “Create Stored Procedure (New Menu)”.

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure

    If you receive a “The folder is marked as ‘Read Only’” or “Access is denied” error,  you can make a copy of “Create Stored Procedure (New Menu)”, edit the copy and then use the copy to replace existing “Create Stored Procedure (New Menu)”.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Raj_79 Thursday, June 03, 2010 1:29 PM
    Thursday, June 03, 2010 8:34 AM

All replies

  • The easiest way is to open the Template Explorer in Management Studio ("View" menu, "Template Explorer"), then find the template in the list (it's under "Stored Procedure", "Create Stored Procedure (New Menu)", as mentioned in the header.  Right click the file and select "Edit".  Make your changes and save.  It should work from now on (but in the worst case, you may need to restart Management Studio).

     

    Once you've made your changes, if you want to give them to someone else, you'll need to copy the appropriate file from the C:\Documents and Settings\YourUserName\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\Stored Procedure folder.

     

    Wednesday, June 02, 2010 7:49 AM
    Answerer
  • Thanks Jim

                         I have changed the template as mentioned and also moved this template into C:\Documents and Settings\YourUserName\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\Stored Procedure folder but when i am creating new stored procedure, it showing me again old template format even i have closed the management studio.

                        

    Raj


    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    Wednesday, June 02, 2010 9:25 AM
  • Hi Raj,

    Please navigate to the following location, edit the file called “Create Stored Procedure (New Menu)”.

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure

    If you receive a “The folder is marked as ‘Read Only’” or “Access is denied” error,  you can make a copy of “Create Stored Procedure (New Menu)”, edit the copy and then use the copy to replace existing “Create Stored Procedure (New Menu)”.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Raj_79 Thursday, June 03, 2010 1:29 PM
    Thursday, June 03, 2010 8:34 AM