Returning Values from Nested SQL Procedures

Besides using output parameters to pass values back from the called procedure, you can also return one integer as the return code (also known as the return value).

To return the value, use the SQL control statement RETURN as introduced in Chapter 2, "Basic SQL Procedure Structure." To access the return value, use the SQL control statement GET DIAGNOSTICS. The syntax and a full description of the command can be found in Chapter 6, "Condition Handling."

In Figure 8.3, procedure get_emp_name returns the employee first name for an employee number. If the record is found, a value of 99 is returned. Otherwise, the procedure returns a value of 1000.

Figure 8.3. An example of returning a value from a called procedure.

 CREATE PROCEDURE get_emp_name ( IN p_empno CHAR(6)                               , OUT p_fname VARCHAR(10) )    LANGUAGE SQL    SPECIFIC get_emp_name                          -- applies to LUW and iSeries -- WLM ENVIRONMENT <env>                          -- applies to zSeries gen: BEGIN     -- Declare variables     DECLARE v_return_code INT DEFAULT 99;     -- Declare condition handlers     DECLARE CONTINUE HANDLER FOR NOT FOUND         SET v_return_code = 1000;     -- Procedure logic     SELECT firstnme       INTO p_fname       FROM employee       WHERE empno = p_empno;     RETURN v_return_code; END gen 

Using return codes can be arbitrary. However, it is recommended that system-defined SQLCODEs not be used as customized return codes. It may cause unnecessary confusion in interpreting error messages.

The caller procedure find_emp in Figure 8.4 shows how to use the GET DIAGNOSTICS statement to obtain the return code from the called procedure. Notice that the GET DIAGNOSTICS statement on Line (2) immediately follows the CALL statement on Line (1).

Figure 8.4. An example of receiving a returned value by a caller procedure.

 CREATE PROCEDURE find_emp ( IN p_empno CHAR(6)                           , OUT p_output VARCHAR(50) )    LANGUAGE SQL    SPECIFIC find_emp                             -- applies to LUW and iSeries -- WLM ENVIRONMENT <env>                         -- applies to zSeries fe: BEGIN     -- Declare variables     DECLARE v_rc INT;     DECLARE v_fname VARCHAR(15);     -- Procedure logic     CALL get_emp_name( p_empno, v_fname );            --(1)     GET DIAGNOSTICS v_rc = RETURN_STATUS;             --(2)     IF ( v_rc = 99 )     THEN         SET p_output = 'The employee is: ' || v_fname || '.';     ELSEIF ( v_rc = 1000 )     THEN         SET p_output = 'The employee does not exist!';     ELSE         SET p_output = 'Something else went wrong.';     END IF; END fe 

In Figure 8.4, the caller procedure assesses the execution of the called procedure by checking the return code. This value is then used to formulate more user-friendly messages.

Because an integer can be returned by both the return code and the output parameter, you might wonder which method you should use. A good SQL programming practice is to reserve the use of return codes for status indicators only. For all other situations, use an output parameter even if you only have one integer to return.

Tip

Use a return code for execution status only.