Displays information about stored procedure parameters, including result set variables and SQLSTATE/SQLCODE error values.
sp_iqprocparm [ proc-name ], [ proc-owner ], [ proc-type ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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 |
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:
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:
|
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:
|
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 |
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)
CREATE PROCEDURE statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options
