MTK MTK - 9 months ago 41
MySQL Question

mysql stored procedure SELECT * into out parameter not working

SQL

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

/*This work OK*/
SELECT my_column INTO out_var FROM my_table WHERE my_column = in_var LIMIT 1;

/*This not work*/
SELECT * INTO out_var FROM my_table WHERE my_column = in_var LIMIT 1;

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'] ) );

$sql = "SELECT @outvar";
$query = $dbh->query( $sql );
$result = $query->fetchAll( PDO::FETCH_ASSOC );


When I try 'SELECT * ...' then in php var_dump($stmt) return false;

I'm starting with MySQL and I do not know if you can not do this type of query (SELECT *) in stored procedures, or it's something else?

Gab Gab
Answer Source

Remove the out_var declaration and you will be able to fetch all the columns:

CREATE PROCEDURE `my_sp`(
    IN in_var VARCHAR(32)
)
BEGIN

  SELECT * FROM my_table WHERE my_column = in_var LIMIT 1;

END;

Or you can declare a variable for each column.