Use of Stored Procedures

There are three ways of invoking a Denodo stored procedure:

  1. With the statement CALL:

    Syntax of the CALL statement
    CALL <procedureName:identifier>
            ( [ <paramValue:literal> [ ,<paramValue:literal> ]* ] )
    [ CONTEXT ( 'i18n' = <literal> ) ] [ TRACE ]
    

    The following statement invokes the stored procedure DropIncident, with the parameter “5”:

    CALL DropIncident(5)
    

    If an input parameter of a stored procedure is optional, you can pass NULL.

  1. Invoking the procedure on the FROM clause of a SELECT statement. For example:

    SELECT avgrevenue
    FROM CalculateAvgRevenue( {ROW('B78596011'), ROW('B78596012')} )
    

    In this example, the input parameter of the CalculateAvgRevenue procedure is an array of registers. Each register contains a single field, which is a client’s Tax ID.

  2. Invoking the procedure on the FROM clause of a SELECT statement but providing the input parameters on the WHERE clause. For example:

    SELECT avgrevenue
    FROM CalculateAvgRevenue()
    WHERE taxid_list = { ROW('B78596011'),ROW('B78596012') }
    

    When a stored procedure is invoked on the FROM clause of a query, the schema of the result of the query includes the output parameters of the stored procedure, but also its input parameters (in this example, the taxid_list attribute). Therefore, a stored procedure can be used in the same way than a view, by specifying the values for its input parameters as conditions in the WHERE clause.