iSeries SQL Procedure - Check if already exists

Question

I have an script that falls over if any of the procedures it is trying to create already exists. How can I check/drop if this procedure is already created?

Answer 1

I would guess something along the lines of:

IF EXISTS
(
    SELECT *
    FROM SYSPROCS
    WHERE SPECIFIC_SCHEMA = ???
      AND SPECIFIC_NAME = ???
      AND ROUTINE_SCHEMA = ???
      AND ROUTINE_NAME = ???
)
    DROP PROCEDURE ???

I don't know if you actually need the SPECIFIC_* information or not and I don't know how to handle cases where you have two procedures with the same name but different call signatures, but hopefully this gets you on the right track.

Answer 2

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Procedure_Name]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP PROCEDURE [dbo].[Procedure_Name]

I think this would help you