Use of Stored Procedures¶
There are three ways of invoking a Denodo stored procedure:
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
.
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.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.