DESCRIBE PROCEDURE

The DESCRIBE PROCEDURE statement gets information about the result sets returned by a stored procedure. The information, such as the number of result sets, is put into a descriptor.
Restriction: This topic applies to Windows environments only.

Invocation

This statement can be embedded in an application program only. It is an executable statement that cannot be dynamically prepared.

Authorization

No authorization is required for this statement.

Syntax

DESCRIBE PROCEDURE {procedure-name | host-variable} 
    INTO descriptor-name

Parameters:

procedure-name Identifies the stored procedure to describe by the specified procedure name.
host-variable Identifies the stored procedure to describe by the procedure name contained in the host variable.
descriptor-name Identifies an SQL descriptor area (SQLDA). The information returned in the SQLDA describes the result sets returned by the stored procedure. Considerations for allocating and initializing the SQLDA are similar to those for DESCRIBE TABLE.

Description

When the DESCRIBE PROCEDURE statement is executed, the procedure name or specification must identify a stored procedure that the requestor has already invoked by using a CALL statement.

A procedure name is a qualified or unqualified name. Each part of the name is a long SQL identifier that must be composed of SBCS characters:

  • A fully qualified procedure name is a three-part name. The first part is a location name that identifies the DBMS at which the procedure is stored. The next two parts identify the stored procedure. The last part is a long identifier that contains the name of the stored procedure. A period must separate each of the parts.
  • An implicitly qualified procedure name is a two-part name that is qualified by the location name of the current server. The name and its implicit qualifier identifies a stored procedure. A period must separate the two parts. The first part identifies the stored procedure at the server. The meaning of the first part depends on the application server.
  • An unqualified procedure name is a one-part name with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second implicit qualifier identifies the stored procedure at the server. The meaning of the second implicit qualifier depends on the application server (for MVS, the qualifier is SYSPROC). The name and its implicit qualifiers identifies a stored procedure.

If a host variable is used:

  • It must be a character string variable with a length attribute that is not greater than 254.
  • It must be preceded by a colon and must not be followed by an indicator variable.
  • The value of the host variable is a specification that depends on the application server. Regardless of the application server, the specification must:
    • Be left justified within the host variable
    • Not contain embedded blanks
    • Be padded on the right with blanks if its length is less than that of the host variable

The procedure name in the DESCRIBE PROCEDURE statement must be specified the same way that it was specified on the CALL statement. For example, if a two-part name was specified on the CALL statement, you must use a two-part name in the DESCRIBE PROCEDURE statement. However, there is one condition under which the names do not have to match. If the CALL statement was made with a three-part name and the current server is the same as the location in the three-part name, you can omit the location name and specify a two-part name.

Example:

The statements in the following examples are assumed to be in PL/I programs.

Place information about the result sets returned by stored procedure P1 into the descriptor named by SQLDA1. Assume that the stored procedure is called with a one-part name from current server SITE2.

EXEC SQL CONNECT TO SITE2;
EXEC SQL CALL P1;
EXEC SQL DESCRIBE PROCEDURE P1 INTO :SQLDA1;