Check for existence of a Stored Proc.

[monkeylizard]

I'm creating a routine where I need to drop a Stored Procedure. I only want the command to execute if the SP exists.

How can I get this information to know if the SP exists or not?

Thanks in advance.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[sathyarams]

There is a system table called syscat.routines which has an entry for each of the stored procedures you define on the system.

The other way is to drop the stored procedure and ignore the returned message.

HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[martynh]

I think it's worth mentioning here that if you have more than one stored proc with the same name within the same schema, but with different profiles, you will need to reference syscat.routineparms. This will give you the basis for working out the profile for each procedure.

If you join syscat.routines and syscat.routineparms use an outer join since syscat.routineparms has no rows for routines with no parms, so an inner join would not show stored procs which take no paramaters.