CODEWITHSUNDEEP
mysqlstored-procedures
MTK
MTK

Reputation: 3570

mysql add variable value to result set inside stored procedure

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'

Upvotes: 1

Views: 1622

Answers (2)

MTK
MTK

Reputation: 3570

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'

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

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.

Upvotes: 2

Related Questions