ISeries SQL Procedure - Check if Already Exists

Question

I have a script that crashes if any of the procedures that it is trying to create already exist. How can I check / delete if this procedure is already created?

Answer 1

I would suggest something like:

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 need SPECIFIC_ * information or not, and I don’t know how to handle cases when you have two procedures with the same name but different call signatures, but hopefully this will help you track correctly.

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 will help you.

Answer 3

( - ):

SELECT *                       
FROM QSYS2/PROCEDURES          
WHERE PROCNAME LIKE 'your-procedure-name'
AND   PROCSCHEMA = 'your-procedure-library'   

Answer 4

DROP PROCEDURE xxx ; CREATE PROCEDURE XXX . . . ;

Include a DROP PROCEDUREas the first statement in the script. If you are starting with RUNSQLSTM, use ERRLVL (20) to enable DROP. If you run "Run SQL Scripts", use the "Ignore" object, which is not found "on DROP".