Example 2

This example calls chained stored procedures using the Database Outbound protocol.

For example, a stored procedure must be called. Then, another stored procedure’s return code or output parameter value is required as the input parameter. Finally, one stored procedure's output parameter must be set as another stored procedure's input parameter.

To do this, use the chained stored procedures in the Database Outbound protocol. Then, use the spName.paraName placeholder to pass the output parameter value of the previous procedure into the input parameter of the next stored procedure.

This example calls two stored procedures:

  • Procedure sp_dbp_test1 returns two out parameters, @output1 and @output2, and the return value _CLRC_.
  • Procedure sp_dbp_test2 uses sp_dbp_test1’s out parameters sp_dbp_test1.@output1 and sp_dbp_test1.@output2. sp_dbp_test1’s return code sp_dbp_test1._CLRC_ is used as its first three input parameters.
Note: When calling more than one stored procedure, a semicolon (;) is used to separate the stored procedures.
{?=call sp_dbp_test1(Table_sanity.strFLD, Table_sanity.intFLD, 
@output1 OUT, @output2 OUT)}; 
{call sp_dbp_test2(sp_dbp_test1. @output1, sp_dbp_test1.@output2, 
sp_dbp_test1._CLRC_,Table_sanity.dateFLD, Table_sanity.flag)}

Command issued:

hcidbprotocoltest -e ASCII -f nl sqlserver_sp_test C:/cloverleaf/cisversion/integrator/
sqlserver_dbtest/test/test.txt

Command output:

Test with MESSAGE 1
     key01,test1,1,10/01/12,0
The output value of <sp_dbp_test1._CLRC_> is: 90
The output value of <sp_dbp_test1.@output1> is: liketest1
The output value of <sp_dbp_test1.@output2> is: 6
Running DB Write action succeeded.

SQL result:

{_CLRC_ OUT=call sp_dbp_test1(test1, 1, @output1 OUT, @output2 OUT)}
{call sp_dbp_test2(liketest1, 6, 90, 10/01/12, 0)}

Returned results:

Procedure: sp_dbp_test1
> return value: _CLRC_ = 90
> out parameter: @output1 = liketest1
> out parameter: @output2 = 6
Procedure: sp_dbp_test2
> update count: 1