So far you've seen examples of passing values back to the calling program through OUT parameters in the SQL procedure declaration. You can also return values using the RETURN statement. Figure 6.20 shows the syntax diagram for the RETURN statement within a procedure.
Figure 6.20. The syntax diagram for a RETURN statement.
It is optional to specify an expression in the RETURN statement. If an expression is provided, it must be an INTEGER, and therefore you are quite limited in what you can RETURN through this statement. The calling procedure issues GET DIAGNOSTICS with RETURN_STATUS to obtain the returned value. GET DIAGNOSTICS must be issued immediately after the CALL to a procedure.
You must be very careful when using this method for returning parameters. Typical use for this statement would be to RETURN 0 upon success and -1 on failure. In order to ensure -1 on failure, you can write a handler to catch exceptions and issue a RETURN -1 from the handler. However, this is only allowed in iSeries and zSeries. In LUW, RETURN statements are not allowed within handlers. To get around such situations, you would have to use a nested compound statement, as shown in Figure 6.21. Figure 6.31 in the section "DB2 UDB for zSeries Considerations" demonstrates an SQL procedure with the equivalent behavior for iSeries and zSeries.
Figure 6.21. An example of RETURN with a compound statement in LUW.
CREATE PROCEDURE ret_value ( ) LANGUAGE SQL SPECIFIC ret_value --applies to LUW and iSeries rv: BEGIN DECLARE v_ret_value INT DEFAULT 0; body: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_ret_value = -1; -- <body of procedure>; END body; RETURN v_ret_value; END rv
It is often not enough for an application to just see a return value. The application may need more information if an error occurred. The additional information can be passed back using an OUT parameter. This is shown in Figure 6.23.
The body of the procedure is within a compound statement that has a handler which sets the return value on an exception. Note that there are limitations even with this solution. For example, if you wanted to RESIGNAL an SQLSTATE to the calling program, you would not be able to do so because you cannot have a handler declared at the outermost compound statement in the procedure and still RETURN a value.
The alternative to the RETURN statement is the passing of OUT parameters in the procedure declaration, for which you've already seen many examples. What you haven't seen is an example of a CALL to another procedure, and checking the output parameters and/or return codes, as in Figure 6.22.
Figure 6.22. An example of a stored procedure call with checking for parameters.
CREATE PROCEDURE call_procs ( ) LANGUAGE SQL SPECIFIC call_procs -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries cp: BEGIN DECLARE v_empno CHAR(6) DEFAULT 'A'; -- Input to get_diag() DECLARE v_num_rows INT; -- Output from get_diag() DECLARE v_ret_value INT; -- Return value from ret_value() -- Example of a call to a procedure where a parameter is passed back in the -- argument list as an OUT parameter CALL get_diag( v_empno, v_num_rows ); IF v_num_rows = 0 THEN -- <some statements> END IF; -- Example of a call to a procedure where the RETURN statement is used to -- return the value to the caller CALL ret_value(); GET DIAGNOSTICS v_ret_value = RETURN_STATUS; RETURN v_ret_value; END cp
This example makes two calls to other procedures. The first CALL statement is a call to the get_diag procedure (shown in Figure 6.16), which passes back return values in the argument list. The second call is a call to the ret_value procedure (shown in Figure 6.21), which uses a RETURN statement to pass back a value to the calling program.