Menu
How do I save the results of EXECUTE statement to a variable? Something like
SET a = (EXECUTE stmtl); 
490
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another. User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ .
The following works as expected when there is a single value stored in a variable. SET @a := "20100630"; SELECT * FROM wordbase WHERE verified = @a; But it does not work when there are multiple values stored in a variable. SET @a := "'20100630', '20100701' "; SELECT * FROM wordbase WHERE verified in (@a);
Use the following syntax : $result= mysql_query(".. your query.."); Note that this variable can also store multiple rows depending on your query. Also note that statements other than "select" will return the no of rows affected.
If you want to do this with a prepared statement, then you need to include the variable assignment in the original statement declaration.
If you want to use a stored routine it's easier. You can assign the return value of a stored function directly to a variable, and stored procedures support out parameters.
Examples:
Prepared Statement:
PREPARE square_stmt from 'select pow(?,2) into @outvar'; set @invar = 1; execute square_stmt using @invar; select @outvar; +---------+ | @outvar | +---------+ | 1 | +---------+ DEALLOCATE PREPARE square_stmt; Stored Function:
delimiter $$ create function square_func(p_input int) returns int begin return pow(p_input,2); end $$ delimiter ; set @outvar = square_func(2); select @outvar; +---------+ | @outvar | +---------+ | 4 | +---------+ Stored Procedure:
delimiter $$ create procedure square_proc(p_input int, p_output int) begin set p_output = pow(p_input,2); end $$ delimiter ; set @outvar = square_func(3); call square_proc(2,@outvar); select @outvar; +---------+ | @outvar | +---------+ | 9 | +---------+ If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
