Alter procedure

Question

Alter procedure

Lanny Irwan

Ten Centuries

Points: 1020
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

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]

Lanny Irwan Ten Centuries Points: 1020 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 5

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 7

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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)