All stored procedures can receive in parameters and return a return value. The return value is most often used to return a status code from the stored procedure. If the stored procedure was successful, the value of the STATUS_SUCCESS constant, 0 (zero), is returned. If it failed, a value from -1 to -14 (native SQL error codes) or the SQL_SPROC_ERROR constant, -127, is returned.
The return value can be used as an out parameter instead, but it must be defined in Dexterity as a long integer. By using the return value as an out parameter, you give up the ability to verify the success or failure of the stored procedure.
This example shows a stored procedure written using Microsoft SQL Server. The script used to call this stored procedure is also shown.
This stored procedure, named Count_Seniors doesn’t require in parameters and returns only the return value, @Count. It queries the Employees table to find the number of employees over age 65.
CREATE PROC Count_Seniors AS DECLARE @Count int /* Count the number of employees over age 65 */ SELECT @Count = count(first_name) from EMPLOYEES where Age > 65 RETURN @Count
The following Dexterity prototype procedure, also named Count_Seniors, calls the Count_Seniors stored procedure.
sproc returns long Ret_Code; call sproc "Count_Seniors", Ret_Code;
The following script calls the Count_Seniors Dexterity prototype procedure and sets the value of the Senior Employees field. If the stored procedure is successful, Ret_Code will be set to the @Count value returned by the stored procedure. Otherwise, it will be set to -127, the value corresponding to the SQL_SPROC_ERROR constant.
local long Ret_Code; call Count_Seniors, Ret_Code; 'Senior Employees' = Ret_Code;
Since the success of the stored procedure isn’t verified in this example, the Ret_Code value will be displayed to a user, even if its value is -127. This potential for displaying erroneous data is why return values are rarely used as out parameters.
Stored procedures can return out parameters in addition to the return value. However, stored procedures must not generate a results set. Dexterity can handle only return values and parameters as stored procedure outputs.
This example shows a stored procedure named Profile, which returns out parameters. The script syntax for the prototype procedure and the calling script are also shown.
The stored procedure returns a customer profile. It requires an in parameter and returns five out parameters. The return value isn’t manipulated in this procedure; rather, it’s used for its default functionality, status checking. If the stored procedure was successful, the value of the STATUS_SUCCESS constant, 0 (zero), is returned. If it failed, a value from -1 to -14 (native SQL error codes) or the SQL_SPROC_ERROR constant, -127, is returned.
CREATE PROCEDURE Profile(@Cust_ID smallint, @Name char out, @City char out, @State char out, @Tot_Purch numeric out, @Cust_Award char out) AS SELECT @Name, @City, @State FROM Customer_Table WHERE Cust_ID = @Cust_ID SELECT @Tot_Purch=Q1_Sales + Q2_Sales + Q3_Sales + Q4_Sales IF @Tot_Purch >= 100000.00 @Cust_Award = 'Statue' ELSE IF @Tot_Purch between 50000.00 and 99999.99 @Cust_Award = 'Plaque' ELSE @Cust_Award = 'None'
The prototype procedure, also named Profile, is defined as follows. The Sproc_Status parameter is the return value, and must be declared using the sproc returns clause. The remaining parameters must be listed in the same order as in the stored procedure.
sproc returns long Sproc_Status; in integer Cust_ID; inout string Name; inout string City; inout string State; inout currency Tot_Purch; inout string Cust_Award; call sproc "Profile", Sproc_Status, Cust_ID, Name, City, State, Tot_Purch, Cust_Award;
The following is a portion of a script using the call statement to run the Profile procedure.
local long Sproc_Status; Cust_ID = Cust_ID of window Customer_Info; call Profile, Sproc_Status, Cust_ID, Name, City, State, Tot_Purch, Cust_Award;