MySQL Stored Procedure Programming

Name

DAT-06: Employ “user” variables for global data sparingly

Synopsis

A global variable is a data structure that can be referenced outside the scope or block in which it’s declared. In MySQL, we can use “user” variables—which can be recognized by being prefixed with @—to set values that are available to any program within the current session.

In the following procedure, for example, we store the number of customers into the user variable @customer_count:

    CREATE PROCEDURE sp_customer_count(  )

      SELECT COUNT(*)
        INTO @customer_count
        FROM customers;

Other procedures can examine the @customer_count and make decisions without having to recalculate the value. For instance, in this procedure we use the session variable in our setup logic:

    CREATE PROCEDURE sp_crm_setup (  )

    BEGIN
       IF @customer_count IS NULL THEN
         CALL sp_customer_count(  );
       END IF;

       IF @customer_count > 1000 THEN
             . . . Logic for larger enterprises . . ..

There is no doubt that the use of global variables can create easy solutions for difficult problems. However, the modern consensus is that global variables create their own problems and that these problems generally overwhelm any of the advantages they might confer.

Global variables defeat modularity and hinder code reuse, because any module that uses a global variable becomes dependent on some other module that creates or initializes the global variable. In the case of MySQL user variables—which don’t require a formal declaration—there is also the chance that two programmers might ...