Menu
MySQL stored function returns only one value. To develop stored programs that return multiple values, you need to use stored procedures with INOUT or OUT parameters.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
DECLARE var1 int; DECLARE var2 int; DECLARE var3 int; SELECT var1:=id, var2:=foo, var3:=bar from page WHERE name="bob"; CALL someAwesomeSP (var1 , var2 , var3 );
Click a field name to select the first field. Hold down SHIFT and click another field name to add to the selection all the fields between the last field selected and that field. Hold down CONTROL and click a field name to add it to or remove it from the selection.
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
==========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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With