How to differentiate between local variable and field name?-CodePudding

I have a stored procedure in MySQL (actually MariaDB 10.3), and I have a variable declared, let's call it name.

Later in my procedure, I want to use my name variable to filter a table. This table has a field also called name.

How can I use my name variable to filter my table on the name field?

Here is an example:

DELIMITER //

CREATE PROCEDURE doSomething()
BEGIN
    -- Declare a variable and load a value into it
    DECLARE name CHAR(6) DEFAULT NULL;
    SELECT value FROM tableA WHERE id = 6 INTO name;

    -- Use the variable in a query
    SELECT * FROM tableB WHERE name = name;
END; //

DELIMITER ;

CALL doSomething();

As you can see, I load the name variable with a value from tableA. Then I want to filter tableB using this name variable. Obviously, WHERE name = name doesn't work.

How do I refer to my variable alongside the field name? Something like:

WHERE name = _variables_.name

CodePudding user response：

never use variables that are the same as column names mysql gets confused

DELIMITER //

CREATE PROCEDURE doSomething()
BEGIN
    -- Declare a variable and load a value into it
    DECLARE _name CHAR(6) DEFAULT NULL;
    SELECT value FROM tableA WHERE id = 6 INTO _name;

    -- Use the variable in a query
    SELECT * FROM tableB WHERE name = _name;
END; //

DELIMITER ;

CALL doSomething();

CodePudding user response：

The docs are clear that locally declared variables take scope precedence over column names. So, with respect, your SP suffers from ambiguity. It's confusing and wrong.

But if you qualify your column name with a table name in your SELECT, it works. Like so.

SELECT * FROM tableB WHERE tableB.name = name;