June 13, 2002 at 2:17 pm
If I have a stored procedure which I Want to modify, is it best to use ALTER PROCEDURE or drop & recreate? If I do the latter, will it have a new object ID, and if so, what are the implications of this?
Thanks in advance
June 13, 2002 at 2:27 pm
If you do a DROP PROC and CREATE PROC , you'll lose any security settings. Therefore, you'll have to also redo the GRANT EXECUTE and DENY EXECUTE. If you use ALTER PROC, the security settings will be preserved.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 13, 2002 at 4:17 pm
I tend to use drop/create so I can track when a procedure is changed. I do include the security statements in the drop/create script.
If you use alter, you will not know when the change was made, though there is a version that is incremented. You have to manually track this somehow to know if something changed.
Steve Jones
June 13, 2002 at 5:36 pm
I normally use alter, though more often I version by creating a new version of the proc that includes the change. For those cases when it's a minor tweak to a live app, I alter.
Andy
June 13, 2002 at 6:28 pm
I generally use ALTER. I test it then see if what I expect is happening. If something is not happening right then I run sp_helptext theprochere or open EM and verify the Proc against what I have unless it is long then I have an app the compares text to make sure is the same. And I do beleive if you DROP and RECREATE it is issued a new object id. To be sure run PRINT OBJECT_ID(procname) before the drop and after the create.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/13/2002 6:29:29 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply