(mysql.info) select-into-statement

Info Catalog

(mysql.info) set-statement

(mysql.info) variables-in-stored-procedures

 
 17.2.7.3 `SELECT ... INTO' Statement
 ....................................
 
      SELECT COL_NAME[,...] INTO VAR_NAME[,...] TABLE_EXPR
 
 This `SELECT' syntax stores selected columns directly into variables.
 Therefore, only a single row may be retrieved.
 
      SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
 
 User variable names are not case sensitive. See  user-variables.
 
 *Important*: SQL variable names should not be the same as column names.
 If an SQL statement, such as a `SELECT ... INTO' statement, contains a
 reference to a column and a declared local variable with the same name,
 MySQL currently interprets the reference as the name of a variable. For
 example, in the following statement, `xname' is interpreted as a
 reference to the `xname' _variable_ rather than the `xname' _column_:
 
      CREATE PROCEDURE sp1 (x VARCHAR(5))
        BEGIN
          DECLARE xname VARCHAR(5) DEFAULT 'bob';
          DECLARE newname VARCHAR(5);
          DECLARE xid INT;
 
          SELECT xname,id INTO newname,xid
            FROM table1 WHERE xname = xname;
          SELECT newname;
        END;
 
 When this procedure is called, the `newname' variable returns the value
 `'bob'' regardless of the value of the `table1.xname' column.
 
 See also  routine-restrictions.
 

Info Catalog

(mysql.info) set-statement

(mysql.info) variables-in-stored-procedures