Chapter 6: Using Stored Procedures

sp_stored_procedures

Description

Returns a list of available procedures.

Syntax

sp_stored_procedures [sp_name] [, sp_owner] [, sp_qualifier]

Parameters

sp_name is the stored procedure name. Use the wildcard character to request information about more than one stored procedure. If left blank, sp_stored_procedures returns information for all procedures.

sp_owner is the owner of the stored procedure. Use the wildcard character to request information about procedures owned by more than one user.

sp_qualifier is ignored. Leave blank or set to NULL.

Usage

This function corresponds to the ODBC function SQLProcedures.

Results

sp_stored_procedures lists and describes stored procedures. Results are ordered by the following columns:

PROCEDURE_QUALIFIER
PROCEDURE_OWNER
PROCEDURE_NAME

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table describes the result set for sp_stored_procedures.

**Table 6-18: Result set for sp_stored_procedures**
Column	Datatype	Description
PROCEDURE_QUALIFIER	varchar(128)	Always NULL.
PROCEDURE_OWNER	varchar(128)	Procedure owner.
PROCEDURE_NAME	varchar(128) NOT NULL	Procedure name.
NUM_INPUT_PARAMS	int NOT NULL	Number of input parameters in the stored procedure. -1 – the number of input parameters is unknown.
NUM_OUTPUT_PARAMS	int NOT NULL	Number of return parameters in the stored procedure. -1 – the number of return parameters is unknown.
NUM_RESULT_SETS	int NOT NULL	Number of result sets returned by the stored procedure. -1 – the number of result sets is unknown.
REMARKS	varchar(254)	Describes the procedure.
PROCEDURE_TYPE	smallint	Defines the procedure type: 0 SQL_PT_UNKNOWN – it cannot be determined whether the procedure returns a value. 1 SQL_PT_PROCEDURE – the returned object is a procedure; it does not have a return value. 2 SQL_PT_FUNCTION – the returned object is a function; it has a return value.

View this book as PDF