Example documentationDBPROCPARAMS Locate this document in the navigation structure

 

Use of the system table DOMAIN.DBPROCPARAMS

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Create database procedures and database functions and use them. Proceed as described in SQL Tutorial, Database Procedures and Database Functions.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Note the General Instructions for formulating SQL statements.

You can use the system table DBPROCPARAMS to determine the following database information, among other things:

  • All input parameters of the database function AVG_PRICE in the HOTEL schema and the following parameter information: Data type, length and the number of the affected parameter in the range of all parameters within that database function

    SELECT parametername, datatype, len, dec, pos

    FROM DOMAIN.DBPROCPARAMS

    WHERE schemaname = 'HOTEL'

    AND dbprocname = 'AVG_PRICE'

    AND "IN/OUT-TYPE" = 'IN'

  • All output parameters of the database procedure AVG_PRICE in the HOTEL schema and the following parameter information: Data type, length and the number of the affected parameter in the range of all parameters within that database procedure

    SELECT parametername, datatype, len, dec, pos

    FROM DOMAIN.DBPROCPARAMS

    WHERE schemaname = 'HOTEL'

    AND dbprocname = 'AVG_PRICE'

    AND "IN/OUT-TYPE" = 'OUT'

  • Total number of parameters for the database procedure AVG_PRICE in the HOTEL schema

    SELECT COUNT(*)

    FROM DOMAIN.DBPROCPARAMS

    WHERE schemaname = 'HOTEL'

    AND dbprocname = 'AVG_PRICE'

More Information

Database Procedures: see DBPROCEDURES

Database Functions: See FUNCTIONS