SQL0476N trying to drop a duplicate stored procedure

Technote (FAQ)


Question

I received an error SQL0476N when trying to drop a duplicate stored procedure. How do I drop the procedure without getting the error?

Cause

DB2® Universal Database™ (DB2 UDB) allows you to create two identically named stored procedures within a schema as long as they don't have the same number of parameters. For example :

CREATE PROCEDURE schema1.PART ( IN NUMBER INT , OUT PART_NAME CHAR (35) ) ...

CREATE PROCEDURE schema1.PART ( IN COST DECIMAL (5,3), IN NUMBER INT, OUT COUNT INT ) ...

The second statement will succeed as long as the number of parameters are not the same. Otherwise, a SQLSTATE 42723 is generated even if the data types are not identical.


Answer

To drop a duplicate stored procedure, you must qualify the DROP PROCEDURE with the same data types that were specified on the CREATE PROCEDURE statement in the corresponding position. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance to be dropped.

For example:

DROP PROCEDURE schema1.PART (DECIMAL, INTEGER, INTEGER)

Another method to drop the procedure is to use the specific-name identifies the specific procedure instance in the implied schema.


Related information

Create Procedure (SQL) statement
DROP statement

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
DB2 Programming Interfaces - Non SQL Stored Procedure

Software version:

9.1, 9.5, 9.7

Operating system(s):

Platform Independent

Software edition:

All Editions

Reference #:

1195923

Modified date:

2009-10-28

Translate my page

Machine Translation

Content navigation