Create/refresh Stored Procedure: Tip

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create/refresh Stored Procedure: Tip

Author

Topic

Peter.Bij@klm.com
Starting Member

12 Posts

Posted - 2003-05-28 : 04:28:55

To update your stored procedure you need to drop the old one first and than recreate the proc
This piece of coding makes it very easy for you:

/* Drop procedure if it already exists */
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id('<MyStoredProcedureName>')
AND sysstat & 0xf = 4)
BEGIN
DROP PROCEDURE <MyStoredProcedureName>
END
GO

/* Create new procedure */

CREATE PROCEDURE <MyStoredProcedureName>

AS
BEGIN
..
..
END
GO

(of course, you don't use the square brackets in the name)

All credits go to my collegue Bert Meerkamp

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 10:32:52

Ever scripted a database?

Brett

8-)

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-28 : 23:49:57

Or you could just script in QA, virtually the same code is produced. I'm not sure your buddy Bert should take to much credit.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-29 : 00:08:42

quote:

To update your stored procedure you need to drop the old one first

WRONG!!!!

ALTER PROC does nicely thanks....

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources