CODEWITHSUNDEEP
mysqlstored-proceduresparameters
nightcoder
nightcoder

Reputation: 13509

MySQL : When stored procedure parameter name is the same as table column name

Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write

UPDATE customers SET Name = Name;

this is incorrect and I see 2 other ways:

UPDATE customers SET Name = `Name`;
UPDATE customers SET customers.Name = Name;

First one works, but I didn't find in documentation that I can wrap parameters inside ` characters. Or did I miss it in the documentation (link is appreciated in this case).

What other ways are there and what is the standard way for such a case? Renaming input parameter is not good for me (because I have automatic object-relational mapping if you know what I mean).

UPDATE:

So, there is a link about backticks (http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) but it's not explained deep enough how to use them (how to use them with parameters and column names).

And there is a very strange thing (at least for me): You can use backticks either way:

UPDATE customers SET Name = `Name`;
//or
UPDATE customers SET `Name` = Name;
//or even
UPDATE customers SET `Name` = `Name`;

and they all work absolutely the same way.

Don't you think this is strange? Is this strange behavior explained somewhere?

Upvotes: 14

Views: 14837

Answers (5)

Broken Arrow
Broken Arrow

Reputation: 608

Not necessarily correct, but a fair way to better argument/parameter management, as well readability with easier understanding, especially while working with the SQL;

DROP PROCEDURE IF EXISTS spTerminalDataDailyStatistics; DELIMITER $$
CREATE PROCEDURE spTerminalDataDailyStatistics(
    IN TimeFrom DATETIME, 
    IN DayCount INT(10), 
    IN CustomerID BIGINT(20)
) COMMENT 'Daily Terminal data statistics in a date range' BEGIN
    # Validate argument
    SET @TimeFrom := IF(TimeFrom IS NULL, DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'), TimeFrom);
    SET @DayCount := IF(DayCount IS NULL, 5, DayCount);
    SET @CustomerID := CustomerID;
    
    # Determine parameter
    SET @TimeTo = DATE_ADD(DATE_ADD(@TimeFrom, INTERVAL @DayCount DAY), INTERVAL -1 SECOND);

    # Do the job
    SELECT          DATE_FORMAT(TD.TerminalDataTime, '%Y-%m-%d') AS DataPeriod, 
                    COUNT(0) AS DataCount, 
                    MIN(TD.TerminalDataTime) AS Earliest, 
                    MAX(TD.TerminalDataTime) AS Latest
    FROM            pnl_terminaldata AS TD
    WHERE           TD.TerminalDataTime BETWEEN @TimeFrom AND @TimeTo
        AND         (@CustomerID IS NULL OR TD.CustomerID = @CustomerID)
    GROUP BY        DataPeriod
    ORDER BY        DataPeriod ASC;
END $$
DELIMITER ;

CALL spTerminalDataDailyStatistics('2021-12-01', 2, 1801);

Upvotes: 0

Ankur Kumar Singh
Ankur Kumar Singh

Reputation: 619

Simplest way to distinguished between your parameter and column (if both name is same) is to add table name in your column name.

UPDATE customers SET customers.Name = Name;

Even you can also add database prefix like

UPDATE yourdb.customers SET yourdb.customers.Name = Name;

By adding database name you can perform action on more than 1 database from single store procedure.

Upvotes: 22

Chad Birch
Chad Birch

Reputation: 74528

I think that your first example is actually backwards. If you're trying to set the "Name" column to the "Name" input parameter, I believe it should be:

UPDATE customers SET `Name` = Name;

And for the second example, you can set table aliases the same way that you do in all other statements:

UPDATE customers AS c SET c.Name = Name;

Upvotes: 7

Christian
Christian

Reputation: 2957

Here is the link you are asking for: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html The backticks are called "identifier quote" in MySql

Upvotes: -1

Cody Caughlan
Cody Caughlan

Reputation: 32748

Using backticks in MySQL query syntax is documented here:

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

So yes, your first example (using backticks) is correct.

Upvotes: -1

Related Questions