Original: PB Help
This article intends to use the Sybase ASE 10.x and 11.x database as an example to explain how to get the return value of the stored procedure in the PB script. As a stored procedure, the result data of its output may include three categories: SELECT result set, return result, Output parameter. Although there are many output methods, the PB scripts can only get these output data with a simple FETCH ... INTO ... statement, and the specific methods are as follows:
(1) Creating a stored procedure deptroster in the Sybase ASE 10.x and 11.x database, which has an input parameter @Deptno, two output parameters @totsal and @avgsal, a return value @number_of_emps and contains staff names and salary SELECT results set, it can be seen that in addition to entering parameters @Deptno, others are output data, we need to get in the PB script, the specific code is as follows:
Create Procedure Deptroster @Deptno Integer,
@Totsal Double Precision Output,
@AVGSAL Double Precision Output
AS
Declare @number_of_emps integer
SELECT EMP_FNAME, EMP_LNAME, SALARY FROM EMPLOYEEEEE
Where dept_id = @Deptno
SELECT @totsal = SUM (SALARY),
@AVGSAL = AVG (SALARY),
@number_of_emps = count (*) from EMPLOYEE
Where dept_id = @Deptno
Return @number_of_emps;
(2) The PB script we need to capture the Select result set, return value, and two output parameters. The output order is also output in order of the "Select result set, Return value, output parameter", and the specific code is as follows:
Integer fetchcount = 0
Long LDEPTNO, RC
String fname, Lname
Double dsalary, dtotsal, Davgsal
LDEPTNO = 100
// This declares the stored procedure name
Declare DeptProc Procedure for
@rc = dbo.deptroster
@Deptno =: ldeptno,
@totsal = 0 OUTPUT,
@AVGSAL = 0 OUTPUT
Using sqlca;
// Start execution of stored procedures
EXECUTE DEPTPROC;
// Judgment the execution result
Choose Case Sqlca.sqlcode
Case 0
// If you return 0, the execution is successful, at least one SELECT result set
// Start capturing this SELECT result set by means of a loop loop
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 chaoose
Loop while sqlca.sqlcode = 0
// Reclusion once separate FETCH statement to get return value and Output parameters
Fetch DeptProc Into: RC,: DTOTSAL,: DAVGSAL
Choose Case Sqlca.sqlcode
Case 0
MessageBox ("Fetch Return Value and Output" & Output "&
"PARMS SUCCESSFUL", "Return Value IS:" &
String (RC) &
"~ R ~ NTOTAL SALY:" 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" & Output "&
"Parms Failed", "Sqldbcode IS" &
String (Sqlca.sqldbcode "=" &
Sqlca.sqlerrtext)
End chaoose
// Turn off the stored procedure here
Close DeptProc;
Case 100
/ / If 100 is returned, it means that the result set is not returned.
/ / Don't need a separate CLOSE statement separately.
MessageBox ("Execute Successful", "No Result Set")
Case Else
// Other cases indicate that the stored procedure failed, prompting users
MessageBox ("Execute Failed", & &
String (Sqlca.sqldbcode "=" &
Sqlca.sqlerrtext)
End chaoose
This end is over this article.
