Calling a database stored procedure from PHP

If a stored procedure retuns a 'select' query within a database, a PHP function calling that stored procedure will be similar to :

function ExecStoreProcSelect()
{
$oStatement = $this->mssql->prepare("Exec [Procedure name]  @Json= :Json ;");
  $oStatement ->bindValue(':Json', $sJson, \PDO::PARAM_STR);

$bResult = $oStatement->execute();
if (!$bResult)
{
return false;
}
return $oStatement->fetchAll(\PDO::FETCH_ASSOC);
/* OR 
  $sSelect = "SELECT @@ROWCOUNT as nb_line_return"; // give the nb of line being returned during the last instruction

  $aResults = $this->mssqlBosql->query($sSelect)->fetch(\PDO::FETCH_ASSOC);
  if ($aResults['nb_line_return'] < 1) { return array('errors' => array('No return by the stored   procedure !')); }
  else { return true; }
*/
} 

If no return is expected from the stored procedure, this function could be simplify like this :

function ExecStoreProcUpdate()
{
$oStatement = $this->mssql->prepare("Exec [Procedure name];");
$bResult = $oStatement->execute();
if (!$bResult)
{
return false;
}
return true;
} 


In mysql, the syntax will be slightly different (cf . http://www.mysqltutorial.org/php-calling-mysql-stored-procedures/) using CALL instead of EXEC :

for a stored procedure like this one :

"
DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    in  p_customerNumber int(11),

    out p_customerLevel  varchar(10))

BEGIN

    DECLARE creditlim double;

    SELECT creditlimit INTO creditlim

    FROM customers

    WHERE customerNumber = p_customerNumber;

    IF creditlim > 50000 THEN

    SET p_customerLevel = 'PLATINUM';

    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN

        SET p_customerLevel = 'GOLD';

    ELSEIF creditlim < 10000 THEN

        SET p_customerLevel = 'SILVER';

    END IF; 

END$$

"

The access through PHP can be done via :

require_once 'dbconfig.php';

$customerNumber = 103;

try {

    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    // execute the stored procedure

    $sql = 'CALL GetCustomerLevel(:id,@level)';

    $stmt = $conn->prepare($sql);

    $stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT);

    $stmt->execute();

    $stmt->closeCursor();

    // execute the second query to get customer's level

    $r = $conn->query("SELECT @level AS level")->fetch(PDO::FETCH_ASSOC);

    if ($r) {

        echo sprintf('Customer #%d is %s', $customerNumber, $r['level']);

    }

} catch (PDOException $pe) {

    die("Error occurred:" . $pe->getMessage());

}