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.

2. 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.

3. 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.