MySQL SELECT, store in a variable

Question

For a stored procedure, I want to do a SELECT, and store a column's value into a variable.

How do I do this?

I want to do something like this:

    DECLARE countTemp INT;
    SET countTemp=(SELECT COUNT(Name) FROM mytable WHERE Name= var_name LIMIT 0,1);

OR, like this :

    DECLARE countTemp INT;
    SELECT countTemp=ColumnXYZ FROM mytable WHERE Name= var_name LIMIT 0,1;

But, I tried these and MySQL says my syntax is incorrect; how do I do something like this?

Solution 1

Like this :

DECLARE myvar nvarchar(50);

SELECT ATextColumn INTO myvar FROM myTable LIMIT 1,1;

SELECT CONCAT('myvar is ',myvar ,' .');

http://www.java2s.com/Code/SQL/Procedure-Function/UseselectintotoassignvaluetoanIntegervariable.htm

Solution 2

You can easily set the variable in the select query

SELECT @countTemp := ColumnXYZ FROM mytable WHERE Name= var_name LIMIT 0,1;

@countTemp is your variable!