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

User · Accepted 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

User · Answer

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

User · Answer

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

[sql] MySQL: Selecting multiple fields into multiple variables in a stored procedure

Examples related to sql

Examples related to mysql

Examples related to stored-procedures

Examples related to sql-function