MySQL Stored Procedure Programming

Name

DAT-07: Create stored programs in strict mode to avoid invalid data assignments

Synopsis

Stored program type checking is very dependent on the setting of the sql_mode configuration variable. If a program is created when the sql_mode variable includes one of the "strict" settings (STRICT_TRANS_TABLES or STRICT_ALL_TABLES), then the program will reject invalid variable assignments with an error. If neither of the strict modes is in effect, then the stored program will generate a warning when invalid data assignments occur, but will continue execution.

For instance, in the following program, we accidentally declared a variable as CHAR(1) instead of INT:

    CREATE PROCEDURE TenPlusTen(  )
    BEGIN
      DECLARE a INTEGER DEFAULT 10;
      DECLARE b CHAR(1) DEFAULT 10;
      DECLARE c INTEGER;
      SET  c=a+b;
      SELECT c ;
    END;

If created in "non-strict" mode, this program generates a warning, but continues execution and returns the wrong result (10+10=11?):

    mysql> CALL TenPlusTen(  );
    +------+
    | C    |
    +------+
    |   11 |
    +------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected, 1 warning (0.01 sec)

    mysql> SHOW WARNINGS;
    +---------+------+----------------------------------------+
    | Level   | Code | Message                                |
    +---------+------+----------------------------------------+
    | Warning | 1265 | Data truncated for column 'B' at row 1 |
    +---------+------+----------------------------------------+
    1 row in set (0.00 sec)

If created in strict mode, the program generates an error during execution, which is clearly better than returning the ...