sp_iqprocparm procedure

Function

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

proc-name The name of the procedure.

proc-owner The owner of the procedure.

proc-type The type of procedure. Allowed values are:

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.

Table 7-50: 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:

Table 7-51: 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)

See also

CREATE PROCEDURE statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options