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'; ...

Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.