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

Question

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.

Did you include a DELIMITER $$ (or similar) statement before your procedure? — Nick
– Nick, Commented Feb 2, 2019 at 3:32
In SQL, isn’t assignment just a regular = sign (no : symbol) — theMayer
– theMayer, Commented Feb 2, 2019 at 3:41
Yes - it wasn't an issue with declaring the sproc. The answer below did the trick. BTW the := does work from the query window. I'm used to SQL Server - what works in the query window works in a sproc but not true with MySQL. — Jon Vote
– Jon Vote, Commented Feb 2, 2019 at 4:41

Bill Karwin · Accepted Answer · 2019-02-02 04:26:03Z

4

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

answered Feb 2, 2019 at 4:26

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Jon Vote Over a year ago

Hey thanks! I'm used to SQL Server - this is a bit different.

Bill Karwin Over a year ago

Yeah, MySQL is totally different. You really need to make yourself at home in their reference documentation. I've been using MySQL for many years, and I open their doc daily.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related