Connecting to Your Database
Using Embedded SQL with SAP Adaptive Server Enterprise

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