Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set the variable result, from query

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 ; 

?

like image 307
ოთო შავაძე Avatar asked Jun 27 '12 12:06

ოთო შავაძე


People also ask

How do you DECLARE a variable in a SELECT statement?

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

How do you store SQL query results in a variable in Python?

First of all, c. fetchall() retrieves ALL the results from your query, we'll put them in a variable called rows . Then we create a iterator (the thing you tried to do with the while loop) by doing for row in rows . Then we simply print each row.


2 Answers

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.

like image 137
Roland Bouman Avatar answered Sep 17 '22 00:09

Roland Bouman


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

SELECT COUNT(*) FROM mytable INTO some_var; 
like image 21
juergen d Avatar answered Sep 20 '22 00:09

juergen d