MYSQL stored procedure Result consisted of more than one r... Abstract: MYSQL Stored Procedure error SELECT... INTO... This SELECT syntax stores the selected columns directly to variables. Therefore, only a single row can be retrieved. Note that when using LIMIT 1
SELECT id, data INTO x, y FROM topic LIMIT 1;
In addition, there is a rule in this assignment statement that the parameter name and field name cannot conflict with each other. Otherwise, the parameter can be executed in the past, but the parameter value is not assigned. This is a very implicit error. You can refer to the manual: important: the SQL variable name cannot be the same as the column name. If an SQL statement such as SELECT... INTO contains a reference to a column and a local variable with the same name as the column, MySQL interprets the reference as the name of a variable.
DELIMITER // create procedure mytopic (topicId INT (4), title VARCHAR (50) begin select topicId, title INTO topicId, title FROM tbl_topic limit 1; END // DELIMITER;
When this program is called, regardless of the value of the topicId or title column, the newname variable returns 'null '.
That is: CALL mytopic (@ topicId, @ title); SELECT @ topicId, @ title; display result: @ topicId @ titleNULL NULL