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 ;

?

Answer 1

There are multiple ways to do this.

You can use a subquery:

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 subquery 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 resultset, you can use a cursor.

Set the variable result, from query

Sign up