By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,480 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,480 IT Pros & Developers. It's quick & easy.

How to upgrade a stored procedure this way?

P: n/a
Hi!

I need to write a t-sql-script like this:

(case-1)
IF {stored procedure SP is not exists} BEGIN
CREATE PROCEDURE SP
AS BEGIN
//dummy body
END
END

ALTER PROCEDURE SP
AS
BEGIN
//real body
END

and dislike the case below because it is possible that CREATE fails
after DROP and we lost any (old or new) SP versions:

(case-2)
IF {stored procedure SP is exists} BEGIN
DROP PROCEDURE SP
END

CREATE PROCEDURE SP
AS
BEGIN
//real body
END
How to write the script lile (1) correctly? Now I have a troubles while
inserting "CREATE PROCUDURE" inside "IF true BEGIN ... END" block.
*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Evgeny Gopengauz" <ev***@ucs.ru> wrote in message
news:ao**************@news.uswest.net...
Hi!

I need to write a t-sql-script like this:

(case-1)
IF {stored procedure SP is not exists} BEGIN
CREATE PROCEDURE SP
AS BEGIN
//dummy body
END
END

ALTER PROCEDURE SP
AS
BEGIN
//real body
END

and dislike the case below because it is possible that CREATE fails
after DROP and we lost any (old or new) SP versions:

(case-2)
IF {stored procedure SP is exists} BEGIN
DROP PROCEDURE SP
END

CREATE PROCEDURE SP
AS
BEGIN
//real body
END
How to write the script lile (1) correctly? Now I have a troubles while
inserting "CREATE PROCUDURE" inside "IF true BEGIN ... END" block.
*** Sent via Developersdex http://www.developersdex.com ***


Personally, I would go for case 2 - it's easier to script. If you're using
source control to manage your SQL objects, you should never lose the old
version of the proc code because it will be in your source code repository.
If you aren't using source control (and you should be), then one way to make
sure you don't lose the old one is to put the whole DROP ... CREATE ...
batch inside a transaction - if the create fails for some reason, then just
ROLLBACK.

Simon
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.