OLE DB
EXECUTE
Database stored procedures that perform only updates and do not
return a result set are handled in much the same way as procedures that
return a result set. The only difference is that after the EXECUTE
procedure_name statement executes, no result set is pending, so a CLOSE
statement is not required.
Using the SQLCode property
If a specific procedure can never return a result set, only the
EXECUTE statement is required. If a procedure may or may not return a
result set, you can test the SQLCode property of the referenced
transaction object for +100 (the code for NOT FOUND) after the
EXECUTE.
The possible values for SQLCode after an EXECUTE are:
|
Return code |
Means |
|---|---|
|
0 |
The EXECUTE was successful and a result set is This code is returned even if the result |
|
+100 |
Fetched row not found. |
|
-1 |
The EXECUTE was not successful and no result set was |
Example 1
This example illustrates how to execute a stored procedure that does
not return a result set. It assumes the default transaction object (SQLCA)
has been assigned valid values and a successful CONNECT has been
executed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// good_employee is a database stored procedure. // Declare the procedure. DECLARE good_emp_proc PROCEDURE FOR good_employee; // Execute it. EXECUTE good_emp_proc; // Test return code. Allow for +100 since you // do not expect a result set. if SQLCA.sqlcode = -1 then // Issue an error message since it failed. MessageBox("Stored Procedure Error!", & SQLCA.sqlerrtext) end if |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.
Example 2
This example illustrates how to pass parameters to a database stored
procedure. It assumes the default transaction object (SQLCA) has been
assigned valid values and a successful CONNECT has been executed.
Emp_id_var was set to 691 elsewhere.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
// get_employee is a database stored procedure. // Declare the procedure. DECLARE get_emp_proc PROCEDURE FOR get_employee @emp_id_parm = :emp_id_var; // Declare a destination variable for emp_name. string emp_name_var // Execute the stored procedure using the // current value for emp_id_var. EXECUTE get_emp_proc; // Test return code to see if it worked. if SQLCA.sqlcode = 0 then // Since we got a row, fetch it and display it. FETCH get_emp_proc INTO :emp_name_var; // Display the employee name. MessageBox("Got my employee!",emp_name_var) // You are all done, so close the procedure. CLOSE Get_emp_proc; end if |