Calls a stored procedure of a database. Parameter a contains the SQL for the procedure call, b the database alias. Optional parameters c to j are used as input parameters in the procedure call. The input parameters in the stored procedure call in a are to be specified in the syntax @index:type@, e.g. @0:s@,@1:f@. The index starts at 0. A maximum of 8 input parameters is supported. If more are needed, use function "call db procedure a, alias b,c,d,e,f". Important note: We talk about parameters of this function here and input parameters and output parameters of the stored procedure.

Parameter k is a comma-separated list of positions that define, which columns of the result set of the stored procedure call are interpreted as output parameters. The index starts at 1. This creates an internal list of output parameter values. Parameter l specifies the position of the output parameter value in this internal list that is returned as result of the function. The index starts at 1. See the example below.

Note: Some databases return the input parameters and output parameters of the stored procedure in the result set (for example MySQL, see example below). Other databases, on the other hand, only return the output parameters of the stored procedure in the result set. Parameter k must be adjusted accordingly.

If parameter n is set to true, the procedure will be executed again if an error occurs.

Parameters

Parameter	Description
a	SQL for the call of the stored procedure.
b	Database alias.
c	(optional) Input parameter for the stored procedure.
d	(optional) Input parameter for the stored procedure.
e	(optional) Input parameter for the stored procedure.
f	(optional) Input parameter for the stored procedure.
g	(optional) Input parameter for the stored procedure.
h	(optional) Input parameter for the stored procedure.
i	(optional) Input parameter for the stored procedure.
j	(optional) Input parameter for the stored procedure.
k	Comma-separated list of positions that define which columns of the result set of the procedure call are interpreted as output parameters of the stored procedure. As a result, an internal list of output parameter values is generated. The index starts at 1. Note: If k is left empty, no output parameter is defined.
l	The position in the internal list of output parameter values generated in k that is returned as the result of the function. The index starts at 1. If set to -1, the function returns true if the procedure call was successful, false otherwise. The latter would otherwise lead to an exception.
m	Maximum number of rows to be read. Default: 1. Note: Some databases perform data-changing statements (UPDATE, DELETE) only on a limited number of rows. In that case, use 0 (unlimited) to make sure that more than one row is getting updated/deleted.
n	(optional) true for retry in case of an error. Default: true.
o	(optional) If the name of a list or map (see parameter p) is specified here, the output parameters of the stored procedure are stored in it. The list is always cleared beforehand . Default: <empty>. Note: In both cases only the first row of the result set is used.
p	(optional) true for a map and false for a list (see parameter o). In the case of a map, the column name is used as the key. Default: false.

Types of parameters

i	Integer.
l	Long.
f	Float.
r	Double or Real.
d	Date.
t	Timestamp.
s	Varchar or String.
b	BLOB.

Examples

Assume the following stored procedure (using MySQL).

CREATE PROCEDURE `adding_test`
  (INOUT p1 VARCHAR(10), IN p2 INTEGER, IN p3 INTEGER, OUT p4 REAL)
BEGIN
DECLARE answer VARCHAR(10);
DECLARE result REAL;
  IF p1 = 'plus' THEN
    SET answer = 'added';
    SET result = p2 + p3;
  ELSE
    SET answer = 'subtracted';
    SET result = p2 - p3;
  END IF;
  select answer into p1;
  select result into p4;
END

Now the stored procedure is called. The example applies to a call in a MySQL database. Note: If the used database is an MSSQL, embed the statements in brackets, e.g. {call adding_test(@0:s@,@1:i@,@2:i@,@3:r@)} (so put the SQL statement in curly brackets) . If the database is connected via a connector, the command execute must be used instead of call. For other databases, please refer to the documentation of the respective manufacturer for the correct syntax.

Parameter a	call adding_test(@0:s@,@1:i@,@2:i@,@3:r@)
Parameter b	hub
Parameter c	plus
Parameter d	1
Parameter e	2
Parameter f	0
Parameter k	4
Parameter l	1
Parameter m
Parameter n
Parameter o
Parameter p
Result	3.0

The resolved call will be call adding_test('plus',1,2,0). Parameter k describes that position 4 of the result set is to be interpreted as an output parameter of the stored procedure and parameter l describes that the first output parameter (and there is only one here, namely position 4 in the result set) is to be used as the result of the function.

So from the result set (added,1,2,3.0) position 4 is taken, resulting in the internal list (3.0). Then the first position is taken from this list, so the result of the function is 3.0.