sp_iqprocparm Procedure

Displays information about stored procedure parameters, including result set variables and SQLSTATE/SQLCODE error values.

Syntax

sp_iqprocparm [ proc-name ], [ proc-owner ], [ proc-type ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

Parameters
Parameter	Description
proc-name	The name of the procedure.
proc-owner	The owner of the procedure.
proc-type	The type of procedure. Allowed values are: SYSTEM: displays information about system procedures (procedures owned by user `SYS` or `dbo`) only ALL: displays information about user and system procedures Any other value: displays information about user procedures

You can invoke sp_iqprocparm without parameters. If you do not specify any parameters, input/output and result parameters of user-defined procedures (procedures not owned by dbo or SYS) appear.

If you do not specify either of the first two parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqprocparm NULL, NULL, SYSTEM and sp_iqprocparm NULL, user1.

sp_iqprocparm usage examples
Syntax	Output
sp_iqprocparm	Displays parameters for all procedures in the database not owned by `dbo` or `SYS`
sp_iqprocparm sp_test	Displays information about the procedure sp_test
sp_iqprocparm non_existing_proc	No rows returned, as the procedure non_existing_proc does not exist
sp_iqprocparm NULL, DBA	Displays parameters for all procedures owned by `DBA`
sp_iqprocparm sp_test, DBA	Displays parameters for the procedure sp_test owned by `DBA`
sp_iqprocparm sp_iqtable	sp_iqtable is a system procedure. If there is no user-defined procedure also named sp_iqtable, no rows are returned. (By default, only user-defined procedures are returned.)
sp_iqprocparm sp_iqtable, dbo	No rows returned, as the procedure sp_iqtable is not a user procedure. (By default, only user procedures are returned.)
sp_iqprocparm NULL, NULL, SYSTEM	Displays parameters for all system procedures (owned by `dbo` or `SYS`)
sp_iqprocparm sp_iqtable, NULL, SYSTEM	Displays parameters of the system procedure sp_iqtable
sp_iqprocparm sp_iqtable, dbo, ALL	Displays parameters of the system procedure sp_iqtable owned by `dbo`

Description

The sp_iqprocparm stored procedure displays information about stored procedure parameters, including result set variables and SQLSTATE/SQLCODE error values. If you specify one or more parameters, the result is filtered by the specified parameters. For example, if proc-name is specified, only information about parameters to the specified procedure displays. If proc-owner is specified, sp_iqprocparm only returns information about parameters to procedures owned by the specified owner. If no parameters are specified, sp_iqprocparm displays information about parameters to all the user-defined procedures in the database.

The sp_iqprocparm procedure returns information in the following columns:

sp_iqprocparm columns
Column name	Description
proc_name	The name of the procedure
proc_owner	The owner of the procedure
parm_name	The name of the parameter
parm_type	The type of parameter is one of the following values: normal parameter (variable) result variable: used with procedures that return result sets SQLSTATE error value SQLCODE error value
parm_mode	The mode of the parameter: whether a parameter supplies a value to the procedure, returns a value, does both, or does neither. Parameter mode is one of the following: in: parameter supplies a value to the procedure out: parameter returns a value inout: parameter supplies as well as returns a value NULL: parameter neither supplies nor returns a value
domain_name	The name of the data type of the parameter as listed in the SYSDOMAIN system table
width	The length of string parameters, the precision of numeric parameters, and the number of bytes of storage for all other data types
scale	The number of digits after the decimal point for numeric data type parameters and zero for all other data types
default	The default value of the parameter, held as a string

Examples

Display information about the parameters of the user-defined procedure sp_test:

sp_iqprocparm sp_test

proc_name proc_owner parm_name parm_type parm_mode domain_name width scale  default

sp_test   DBA         ID         normal    in        integer     4     0      (NULL)

Display information about the parameters of the system procedure sp_iqshowcompression:

sp_iqprocparm sp_iqshowcompression, dbo, system

proc_name             proc_owner  parm_name    parm_type  parm_mode  
domain_name  width  scale  default

sp_iqshowcompression  dbo         @owner_name  normal     in  
char         128    0     (NULL)
sp_iqshowcompression  dbo         @table_name  normal     in  
char         128    0     (NULL)
sp_iqshowcompression  dbo         @column_name normal     in  
char         128    0     (NULL)
sp_iqshowcompression  dbo         Column       result     out  
char         128    0     (NULL)
sp_iqshowcompression  dbo         Compression  result     out  
char         3      0     (NULL)