MYSQL, set two variables in stored proc with single select statement

mysql variables select procedure
davej picture davej · Mar 2, 2012 · Viewed 13.9k times · Source

So I'd like it to be something like, or have the effect of:

declare vFN varchar(20); 
declare vLN varchar(20);
set vFN, vLN = (select fname, lname from sometable where id = 1);

Obviously, I could do 2 selects, but that seems very inefficient.

tia

Answer

Jerome WAGNER picture Jerome WAGNER · Mar 2, 2012

you should try

declare vFN varchar(20); 
declare vLN varchar(20);
select fname, lname INTO vFN, vLN from sometable where id = 1;

check http://dev.mysql.com/doc/refman/5.0/en/select-into.html for documentation.

Related questions

MySQL - Define a variable within select and use it within the same select

Is there a possibility to do something like this? SELECT @z:=SUM(item), 2*@z FROM TableA; I always get NULL for the second column. The strange thing is, that while doing something like SELECT @z:=someProcedure(item), 2*@z FROM TableA; …

Read More
MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

Read More
How can I simulate an array variable in MySQL?

It appears that MySQL doesn't have array variables. What should I use instead? There seem to be two alternatives suggested: A set-type scalar and temporary tables. The question I linked to suggests the former. But is it good practice to …

Read More