Stored procedure maintenance

One of the nice features of Oracle stored procedures, functions and packages is that you can create or recreate the database side code in the same statement. The advantage of this is that during upgrades/patches you can run ALL database code scripts again, which ensures nothing is missed - and also that the changes you have made haven't caused breaking changes in another procedure!

For example, the following will create or replace the following security package header..

CREATE OR REPLACE PACKAGE PKG_SECURITY
AUTHID DEFINER
AS
/*  ------------------------------------------------------
    Adds authentication for specified user to system    
    ------------------------------------------------------ */
PROCEDURE proc_AddUserAuthentication(
  userId                IN USERSECURITY.USER_FK%TYPE,
  encryptedPassword     IN USERSECURITY.PASSWORD%TYPE,
  saltValue             IN USERSALT.SALT%TYPE,
  auditUserId           IN USERSECURITY.AUDITUSERID%TYPE);
END;
/

My recent digging around has revealed that you can also mimic this CREATE or REPLACE functionality in MySQL and Sql Server.

In MySQL you can call the DROP PROCEDURE IF EXISTS method....

DROP PROCEDURE IF EXISTS premierenvoy.proc_AddUserAuthentication;
CREATE
DEFINER = CURRENT_USER
PROCEDURE proc_AddUserAuthentication(
............

...and in Sql Server you can use the OBJECT_ID function and check whether the returning value is not null to determine whether to drop the procedure.

IF OBJECT_ID('proc_AddUserAuthentication') IS NOT NULL
DROP PROCEDURE proc_AddUserAuthentication;
GO
CREATE PROCEDURE proc_AddUserAuthentication(
............