Microsoft
SQL Server Update
Using the SQL Code property
If you know for sure that a particular procedure can never return
result sets, then the EXECUTE statement is all that is needed. If there is
a procedure that 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 following table shows all the possible values for SQLCode after
an EXECUTE:
|
Return code |
Means |
|---|---|
|
0 |
The EXECUTE was successful and at least one result This code is returned even if the result |
|
+100 |
Fetched row not found. |
|
-1 |
The EXECUTE was not successful and no result sets |
Example 1
Assume the default transaction object (SQLCA) has been assigned
valid values and a successful CONNECT has been executed. Also assume the
description of the SQL Server procedure good_employee is:
|
1 2 3 4 5 |
// SQL Server good_employee stored procedure: CREATE PROCEDURE good_employee AS UPDATE employee SET emp_salary=emp_salary * 1.1 WHERE emp_status = 'EXC' |
This example illustrates how to execute a stored procedure that does
not return any result sets:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// 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 result sets. if SQLCA.sqlcode = -1 then // Issue 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
Assume the default transaction object (SQLCA) has been assigned
valid values and a successful CONNECT has been executed. Also assume the
description of the SQL Server procedure get_employee is:
|
1 2 3 4 |
// SQL Server get_employee stored procedure: CREATE PROCEDURE get_employee @emp_id_parm int AS SELECT emp_name FROM employee WHERE emp_id = @emp_id_parm |
This example illustrates how to pass parameters to a database stored
procedure. Emp_id_var has been set elsewhere to 691:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
// 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 |
PowerBuilder also provides access to return values and output
parameters. The return values and output parameters are always in the last
result set returned by the stored procedure and they are in this
order:
|
1 |
return value, output parm1, output parm 2 ... |
Example 3
Assume the default transaction object (SQLCA) has been assigned
valid values and a successful CONNECT has been executed. Also assume the
description of the SQL Server procedure return is:
|
1 2 3 4 |
CREATE PROCEDURE emp_return @m1 int, @m2 int, @resultp int output AS SELECT @RESULTP = @m1*@m2 RETURN 0 |
where @m1, @m2, and @resultp are integers.
This example shows how PowerBuilder provides access to return
values:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
//Stored procedure syntax CREATE PROCEDURE sp_outputs @ml int, @m2 int, @result int output as SELECT @result = @ml*@m2; //Declare syntax in script. DECLARE myproc PROCEDURE for sp_outputs @ml = 3, @m2 = 3, @result = 0 output; //Note: The parameters in the declare must match //exactly the parameters in the sp. EXECUTE myproc; //Execute fetches needed until rc = 100 //then fetch output parameters. int myresult FETCH myproc into :myresult; CLOSE myproc; |