MySQL Stored Procedure Programming

Name

PRG-06: Limit functions to a single RETURN statement in the executable section

Synopsis

A good general rule to follow as you write your stored programs is: "one way in and one way out." In other words, there should be just one way to enter or call a program (there is; you don't have any choice in this matter). And there should be one way out, one exit path from a program (or loop) on successful termination. By following this rule, you end up with code that is much easier to trace, debug, and maintain.

For a function, this means you should think of the executable section as a funnel; all the lines of code narrow down to the last executable statement:

    RETURN return value;

Example

Here's a simple function that relies on multiple RETURNs:

    CREATE FUNCTION status_desc (in_cd CHAR(1))
      RETURNS VARCHAR(20)

        DETERMINISTIC
    BEGIN

       IF in_cd = 'C' THEN
          RETURN 'CLOSED';
       ELSEIF in_cd = 'O' THEN
          RETURN 'OPEN';
       ELSEIF in_cd = 'I' THEN
          RETURN 'INACTIVE';
       END IF;
    END;

At first glance, this function looks very reasonable. Yet this function has a deep flaw, due to the reliance upon separate RETURNs: if you don't pass in "C", "O", or "I" for the cd_in argument, the function raises:

    mysql> SELECT status_desc('A');
    ERROR 1321 (2F005): FUNCTION status_desc ended without RETURN

Here's a rewrite that relies upon a single RETURN at the end of the function:

 CREATE FUNCTION status_desc (in_cd CHAR(1)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE v_status VARCHAR(20) ; IF in_cd = 'C' THEN SET v_status='CLOSED'; ...