Passing parameters between procedures within a transaction

Procedures in a transaction can communicate with each other by using volatile parameters on the parameter bulletin board. The parameters are added by using the PUT_PARAM() function and the parameters are read by using the GET_PARAM() function. Each parameter is a name-value pair. If a parameter already exists on the bulletin board, the PUT_PARAM() function replaces the current value with a new one. If a parameter does not exist in the bulletin board, then GET_PARAM() returns NULL.

The following examples show how to write and read parameters:

-- Procedure P1 sets a bulletin board sync parameter.
"CREATE PROCEDURE P1()
BEGIN
PUT_PARAM('CreditLimitExceeded', 'Y');
...
END";

-- Procedure P2 reads the bulletin board sync parameter.
"CREATE PROCEDURE P2()
BEGIN
DECLARE cred_lim_exceeded CHAR(1);
cred_lim_exceeded := GET_PARAM('CreditLimitExceeded');
...
END";

Note that you can use GET_PARAM() not only to read the sync parameter values set by the PUT_PARAM() function but also to read the transaction properties that were set with the SAVE PROPERTY statement.

The parameter bulletin board is visible to all statements of the transaction. This allows different stored procedures, for example, to communicate with each other even if they do not call each other. If one procedure detects an error, it can set a flag that will notify subsequent procedures in that same transaction to skip the processing of the erroneous data.

The parameters appear on the parameter bulletin board of the transaction when the transaction is executed in the master database. The parameters are also visible to the replica while the transaction is executing on the replica.

In most cases, the transaction properties are used when the transaction executes on the master, that is, after the transaction has been propagated from the replica to the master. However, you can use the values on either the replica or the master (or both).

Note When implementing Intelligent Transactions for conflict resolution, turn autocommit off to prevent losing transaction properties. The lifecycle of a transaction parameter is one transaction; that is, it is visible only in the transaction that has set the value. If autocommit is on, then each statement is a separate transaction and bulletin board values are lost immediately.

For details about the PUT_PARAM() and GET_PARAM() functions, see GET_PARAM() function and PUT_PARAM() function.