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

Question

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 1

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

Answer 2

==========Advise==========

@martin clayton Answer is correct, But this is an advise only.

Please avoid the use of ambiguous variable in the stored procedure.

Example :

SELECT Id, dateCreated
INTO id, datecreated
FROM products
WHERE pName = iName

The above example will cause an error (null value error)

Example give below is correct. I hope this make sense.

Example :

SELECT Id, dateCreated
INTO val_id, val_datecreated
FROM products
WHERE pName = iName

You can also make them unambiguous by referencing the table, like:

[ Credit : maganap ]

SELECT p.Id, p.dateCreated INTO id, datecreated FROM products p 
WHERE pName = iName

Answer 3

Alternatively to Martin's answer, you could also add the INTO part at the end of the query to make the query more readable:

SELECT Id, dateCreated FROM products INTO iId, dCreate