SAP Adaptive
Server Enterprise Return values and output parameters
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:
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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 |
Document get from Powerbuilder help
Thank you for watching.
Subscribe
Login
0 Comments
Oldest