The scope of a local variable is within the BEGIN ...
END
block where it is declared. The variable can be
referred to in blocks nested within the declaring block, except
those blocks that declare a variable with the same name.
Local 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 Section D.1, “Restrictions on Stored Routines, Triggers, and Events”.
User Comments
Add your own comment.