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 pending. Regardless of the number of FETCH statements executed, the procedure must be explicitly closed with a CLOSE statement.

This code is returned even if the result set is empty.

+100

Fetched row not found.

-1

The EXECUTE was not successful and no result set was returned.


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.

// 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.

// 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