Post reply

Alter procedure

  • November 6, 2007 at 4:40 am

    #70270

    Hi,

    I require to deploy the stored procedure and it uses 'Alter procedure'. How to verify that the stored procedure has been applied to the database? Any metadata files that I should refer to?

    For 'Create procedure', I usually check the 'crdate' column in the 'sysobjects' table where xtype = 'P' to ensure the timestamp for 'crdate' is having today's date so I could confirm that the stored procedure has been succesfully deployed.

    Need your help here.

    Thanks

  • November 6, 2007 at 4:59 am

    #748590

    One way to do it is to use DROP PROCEDURE and CREATE PROCEDURE instead of ALTER PROCEDURE. Beware, though: you will need to grant permissions again if you do it like this. Or how about just putting comments in the proc that show the version number, alteration date, reason for alteration, and so on?

    John

  • November 6, 2007 at 3:18 pm

    #748896

    Another option is to use extended properties. As part of your alter code .. say right after the final "go" enter an extended property for the SP with something like this:

    declare @LastUpdate varchar(50)

    set @LastUpdate = cast(getdate() as varchar)

    EXEC dbo.sp_addextendedproperty N'LastUpdate', @LastUpdate ,N'USER', N'dbo', N'PROCEDURE', N'sp_Name'

    OR once the property has been created use sp_updateextendedproperty.

    You can then look at all of your extended properties at once using the table sysproperties.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • November 6, 2007 at 8:13 pm

    #748934

    Hi Kenneth,

    Great idea. Really appreciate that. I'll use your method by executing sp_addextendedproperty or sp_updateextendedproperty.

    I will use 'DROP' and 'CREATE' procedure unless I'm pretty sure what are the permissions required.

    Thanks

  • November 6, 2007 at 8:18 pm

    #748937

    A really great way is to include some revision history in the header comments of the proc... you know... the same place where you identify what the proc does, what the I/O parameters are, what any special dependencies or instructions are, and what the example usage code should be 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 6, 2007 at 8:22 pm

    #748938

    I agree but the nice thing about extended properties is that you can run queries on the information. For example sort by last update date. For that matter ou can easily store all that information into the extended properties as well. That and its easy to automate the extended properties update.

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • November 6, 2007 at 8:28 pm

    #748941

    Heh... yeah, and I absolutely agree... the "proper documentation" fairy got on my shoulder, again.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 6, 2007 at 8:57 pm

    #748945

    Oh don't get me wrong. I'm all for "Proper" documentation. I'm just more for accurate (and existing) documentation (I've delt with way to many developers)

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • November 6, 2007 at 9:45 pm

    #748955

    Ummm.... how does putting it into extended properties make it any more accurate than putting it in a header comment? Sure, I understand it make it more accessible, but I don't understand how it would make it more accurate...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 6, 2007 at 9:49 pm

    #748956

    Its the fact that it can be automated. I've worked with too many developers where they tended not to update the headers. Since the extended properties can be automated in the update procedures you don't have to rely on a person to do it correctly.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • November 6, 2007 at 10:26 pm

    #748970

    Ah... got it. Thanks...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply