Set the variable result, from query

Question

When I create the saved procedure, i can create some variable yes? for example:

CREATE PROCEDURE `some_proc` ()  
BEGIN  

   DECLARE some_var INT; 
   SET some_var = 3;
....

QUESTION: but how to set variable result from the query, that is how to make some like this:

DECLARE some_var INT;
SET some_var = SELECT COUNT(*) FROM mytable ;

?

Solution 1

There are multiple ways to do this.

You can use a sub query:

SET @some_var = (SELECT COUNT(*) FROM mytable);

(like your original, just add parenthesis around the query)

or use the SELECT INTO syntax to assign multiple values:

SELECT COUNT(*), MAX(col)
INTO   @some_var, @some_other_var
FROM   tab;

The sub query syntax is slightly faster (I don't know why) but only works to assign a single value. The select into syntax allows you to set multiple values at once, so if you need to grab multiple values from the query you should do that rather than execute the query again and again for each variable.

Finally, if your query returns not a single row but a result set, you can use a cursor.

Solution 2

The following select statement should allow you to save the result from count(*).

SELECT COUNT(*) FROM mytable INTO some_var;

Comments

[Roland Bouman]

MySQL allows you to set the INTO clause as last clause at the end of the statement. However, in standard SQL it appears between the SELECT and the FROM clause.

[ოთო შავაძე]

Roland Bouman, thanks very much, your second variant is working, but first not, parenthesis not help. :)

[Roland Bouman]

No. both work. You must have made a syntax error. mysql> delimiter // mysql> create function f() -> returns int -> begin -> declare v int; -> set v = (select count(*) from dual); -> return v; -> end; -> // Query OK, 0 rows affected (0.05 sec) mysql> select f(); -> // +------+ | f() | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select version(); -> // +-----------+ | version() | +-----------+ | 5.5.20 | +-----------+ 1 row in set (0.00 sec)

[ოთო შავაძე]

I do not know where have i a syntax error, i dont use "delimiter //" and may be this is my error, but variant "INTO" helped me, so thanks very much. :)

[Ravinder Reddy]

@ოთოშავაძე At MySQL console execute the following: set @cnt=(select count(*) from your_table); select @cnt; They should work.

[Gaurav Gupta]

ERROR 1327 (42000) at line 5: Undeclared variable: some_var. - I am getting this error. How to solve this.

[juergen d]

@GauravGupta: DECLARE some_var INT;