mysql add variable value to result set inside stored procedure

Question

I have this scenario (that work OK):

CREATE PROCEDURE `my_sp`(
  IN in_var VARCHAR(32),
  OUT out_var VARCHAR(255)
)
BEGIN   

  DECLARE mysql_query VARCHAR(255);
  DECLARE mysql_result VARCHAR(32);

  SET @mysql_query = CONCAT("
    CALL other_BD.other_Stored_Procedure( '",in_var,"', @other_sp_result );
  ");

  PREPARE stmt FROM @mysql_query;
  EXECUTE stmt;
  SELECT @other_sp_result INTO @mysql_result;
  DEALLOCATE PREPARE stmt;

  IF (@mysql_result = 'OK') 
  THEN
    SELECT * FROM my_table WHERE my_column = 'my_value' LIMIT 1;
    SET out_var = 'whatever';
  ELSE
    SET out_var = 'NOT OK'; 
  END IF;
END;

PHP:

$dbh = new PDO( $connection_params );

$sql = "CALL my_sp( :in_var , @outvar )";

$stmt = $dbh->prepare( $sql );
$stmt->execute( array( ':in_var' => $_POST['in_var'] ) );

//that return the result from SELECT * FROM my_table ... (from my_sp)
$result1 = $stmt->fetchAll( PDO::FETCH_ASSOC );

//That avoid error: Cannot execute queries while other unbuffered queries are active ...
$stmt->closeCursor();


$sql = "SELECT @outvar";
$query = $dbh->query( $sql );
//that return the out_var result ("whatever" for this case);
$result2 = $query->fetchAll( PDO::FETCH_ASSOC );

And the question is:

Is there any way to add

SET out_var = 'whatever'

To the result of this

SELECT * FROM my_table WHERE my_column = 'my_value' LIMIT 1;

And thus make only one query in php to get all data together like:

col_1 => 'val', ... col_n => 'val' ... my_out_var => 'whatever_value'

Vijunav Vastivch · Accepted Answer · 2017-03-27 00:45:24Z

2

Try to use like this:

First Procedure:

create procedure proc_out(input int,out this_out int)
BEGIN

//Your code here..
set this_out = input * 2;

end


$sql ="call proc_out(10, @outvar);
select @outvar;"

Or

Wrap your old stored procedure something like this:

Second Procedure:

create procedure proc_get_outvar(input int)
begin 

    call proc_out(input, @outvar);
    select @outvar;

end;

To use:

$sql ="call proc_out(10);"

You can replace the datatype you need on that procedure:

Hope it helps you.

answered Mar 27, 2017 at 0:45

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MTK Over a year ago

Thank's for your time. I tried what you suggested, but now it only returns me the result of the stored procedure. What I need is something like a union between the result of the second stored procedure (the one that is inside to the first stored procedure) and the result of the SELECT line.I have looked at a few other examples and it seems that this can only be solved using a temporary table inside the stored procedure, add the results to this, and finally make a select on the temporary table

Vijunav Vastivch Over a year ago

You mean you need more than one field?

MTK Over a year ago

Yes. I need to add @outvar => val to recordset obtainded by SELECT. So originaly SELECT can return some like col1=>val1, col2=>val2 ... etc ... and finaly I want to add @outvar=>val to this recordset (like a new column=>value )

Vijunav Vastivch Over a year ago

You need to use outvar to another query or your final query?

MTK · Accepted Answer · 2017-03-30 01:36:45Z

0

Finally I solved myself using this:

SELECT * , @out_var as new_column FROM my_table WHERE my_column = 'my_value' LIMIT 1;

So now I have the 'new_column' added to mi recordset as espected

col_1 => 'val', ... col_n => 'val' ... new_column => 'out_var_value'

edited Mar 30, 2017 at 1:36

answered Mar 30, 2017 at 0:47

MTK

3,6005 gold badges40 silver badges60 bronze badges

Collectives™ on Stack Overflow

mysql add variable value to result set inside stored procedure

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related