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

Jerome WAGNER · Accepted Answer · 2012-03-02 21:24:34Z

17

you should try

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

answered Mar 2, 2012 at 21:24

Jerome WAGNER

22.6k10 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DJ Quimby · Accepted Answer · 2012-03-02 21:22:33Z

1

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;

answered Mar 2, 2012 at 21:22

DJ Quimby

3,70927 silver badges35 bronze badges

a1ex07 · Accepted Answer · 2012-03-02 21:23:43Z

1

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

answered Mar 2, 2012 at 21:23

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Jay Otterbein · Accepted Answer · 2012-03-02 21:25:21Z

1

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;

answered Mar 2, 2012 at 21:25

Jay Otterbein

9686 silver badges11 bronze badges