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

Question

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

Solution 1

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.

Solution 2

Have two set statements. Set one with the select statement and then copy the value in the first to the second.

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

Solution 3

Forgive me if this doesn't work in MySQL, because I'm used to TSQL syntax. But you should be able to do something like:

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

Or if you need to do select into with mysql:

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

Solution 4

select vFN :=fname, vLN:=lname from sometable where id = 1

View more solutions