CODEWITHSUNDEEP
mysqlstored-procedures
Jon Vote
Jon Vote

Reputation: 663

MySQL: How to set a local variable using select in a stored procedure?

It seems like this must be a dumb question :) but I can't seem to find it posted any where.

I need to set a number of local variables in a sproc from a select statement.

This works from the query window: 

select  @value1 := value1, @value2 := value2 from test_table limit 1; 
select  @value1, @value2;

But when I try and do a similar thing in a stored procedure, I get: SQL Error (1064):You have an error in your SQL syntax... 

BEGIN

    declare p_value1 varchar(50); 
    declare p_value2 varchar(50); 
    select  p_value1 := value1, p_value2 := value2 from test_table limit 1; 

END

Any help appreciated.

Upvotes: 2

Views: 3984

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

Use the SELECT...INTO syntax:

BEGIN

    declare p_value1 varchar(50); 
    declare p_value2 varchar(50); 
    select value1, value2 into p_value1, p_value2 from test_table limit 1; 

END

Upvotes: 4

Related Questions