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
usessp_dbp_test1
’s out parameterssp_dbp_test1.@output1
andsp_dbp_test1.@output2
.sp_dbp_test1
’s return codesp_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