In addition to result sets, SAP Adaptive Server Enterprise stored procedures may return a long integer return value and output parameters of any data type. After all of the result sets have been returned, PowerScript requires you to issue one final FETCH procedure_name INTO . . . statement to obtain these values. The order in which these values are returned is:
return value, output parm1, output parm2, ...
Example 1
The following stored procedure contains one input parameter (@deptno) and returns a result set containing employee names and salaries for that department. It also returns two output parameters (@totsal and @avgsal), and a return value that is the count of employees in the department.
integer fetchcount = 0
long lDeptno, rc
string fname, lname
double dSalary, dTotSal, dAvgSal
lDeptno = 100
DECLARE deptproc PROCEDURE FOR
@rc = dbo.deptroster
@deptno = :lDeptno,
@totsal = 0 output,
@avgsal = 0 output
USING SQLCA;
EXECUTE deptproc;
CHOOSE CASE SQLCA.sqlcode
CASE 0
// Execute successful. There is at least one
// result set. Loop to get the query result set
// from the table SELECT.
DO
FETCH deptproc INTO :fname, :lname, :dSalary;
CHOOSE CASE SQLCA.sqlcode
CASE 0
fetchcount++
CASE 100
MessageBox ("End of Result Set", &
string (fetchcount) " rows fetched")
CASE -1
MessageBox ("Fetch Failed", &
string (SQLCA.sqldbcode) " = " &
SQLCA.sqlerrtext)
END CHOOSE
LOOP WHILE SQLCA.sqlcode = 0
// Issue an extra FETCH to get the Return Value
// and Output Parameters.
FETCH deptproc INTO :rc, :dTotSal, :dAvgSal;
CHOOSE CASE SQLCA.sqlcode
CASE 0
MessageBox ("Fetch Return Value and Output" &
"Parms SUCCESSFUL", "Return Value is: " &
string (rc) &
"~r~nTotal Salary: " string (dTotSal) &
"~r~nAverage Sal: " string (dAvgSal))
CASE 100
MessageBox ("Return Value and Output Parms" &
"NOT FOUND", "")
CASE ELSE
MessageBox ("Fetch Return Value and Output" &
"Parms FAILED", "SQLDBCode is " &
string (SQLCA.sqldbcode) " = " &
SQLCA.sqlerrtext)
END CHOOSE
CLOSE deptproc;
CASE 100
// Execute successful; no result set.
// Do not try to close.
MessageBox ("Execute Successful", "No result set")
CASE ELSE
MessageBox ("Execute Failed", &
string (SQLCA.sqldbcode) " = " &
SQLCA.sqlerrtext)
END CHOOSE