MySQL: Selecting multiple fields into multiple variables in a stored procedure

sql mysql stored-procedures sql-function

aHunter · Mar 15, 2010 · Viewed 128.9k times · Source

Can I SELECT multiple columns into multiple variables within the same select query in MySQL?

For example:

DECLARE iId INT(20);
DECLARE dCreate DATETIME;

SELECT Id INTO iId, dateCreated INTO dCreate 
FROM products
WHERE pName=iName;

What is the correct syntax for this?

Answer

Your syntax isn't quite right: you need to list the fields in order before the INTO, and the corresponding target variables after:

SELECT Id, dateCreated
INTO iId, dCreate
FROM products
WHERE pName = iName