Copy stored procedure TSQL in 2000

Question

Post reply

Copy stored procedure TSQL in 2000

singhster

Ten Centuries

Points: 1387
More actions
September 11, 2009 at 6:00 am

#73896

Hi,
We've got development and production database environments. We create tsql scripts to be run against the production environment as this is managed by another company.
Every time we update a stored procedure, we'd like to create a copy of the latest version in production and renaming it.
So spStoredProc would get copied and named spStoredProc_20090911. This is in case of a problem, so we can roll back the stored procedure.
Can anyone suggest a means of doing this in TSQL? Also is there a TSQL method of renaming a stored procedure without rescripting the entire procedure?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 1

sp_rename is your friend for this one...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

Use sp_rename to change the name, but be careful to identify the permissions on the procedure before you recreate it. Dropping and creating stored procedures removes the permissions and they'll have to be added back in.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?

CEWII

singhster Ten Centuries Points: 1387 More actions · Answer 4

Elliott W (9/14/2009)
All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?
CEWII

We do have source control, but in my mind the best way to rollback a change is to revert back to the previous version. Having it in source control doesn't help this task. What's with the CAPS dude? Seems unnecessarily aggressive.

Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 5

singhster (9/15/2009)

Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.

Glad to be of service.

Can I offer you one bit of advice with it - be careful if you script it out again - a lot of tools won't script out re-named procedures properly. Some will attempt to fix up the name and hash it up a right treat, some will fix it properly, some won't try at all. Try creating, renaming and scripting this proc in SSMS:

CREATE PROCEDURE

[dbo] -- schema

.

[procname] -- procedure name

AS

select 'hello'

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

singhster (9/15/2009)
Elliott W (9/14/2009)
All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?
CEWII
We do have source control, but in my mind the best way to rollback a change is to revert back to the previous version. Having it in source control doesn't help this task. What's with the CAPS dude? Seems unnecessarily aggressive.
Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.

I only used caps on one word man, and source control is something that a lot of people here are not good at, or don't do, I wish to stress that they should and caps on a single word is not particularly aggressive.

I can see your rollback method but ask a further question, at some point do you do cleanup of old versions or do you just have old versions galore. We have typically used the last issued version from our source control to do rollbacks and not had ancient versions out there. I can see this working but am not a fan.

CEWII