<?php
/**********************************************************************
* FILENAME :    CallSPReturnCount.php
*
* DESCRIPTION :
*               Illustrates how to obtain a return value from a stored
*               procedure using PHP PDO and ODBC.
*
*               The return value is a count of the number of records
*               where the 'FirstName' field starts with a specific letter
*
* ODBC USAGE :
*               Connect to the datasource
*               Drops existing stored procedure if exists
*               Create new stored procedure
*               Prepares stored procedure
*               Bind return as parameter 1, type SQL_INTEGER
*               For each letter in the parameter list
*                   Binds as parameter 2, type SQL_VARCHAR
*                   Execute pCountNames stored procedure
*                   Output number of records
*               Drop stored procedure used
*               Close statement and database connection
*/

// Random array of letters
$params = array(
    array("pName"=>'b%'),
    array("pName"=>'c%'),
    array("pName"=>'d%'),
    array("pName"=>'e%'),
    array("pName"=>'h%'),
    array("pName"=>'i%'),
    array("pName"=>'k%'),
    array("pName"=>'m%'),
    array("pName"=>'p%'),
    array("pName"=>'r%'),
    array("pName"=>'t%')
);

// Datasource name
$dsn ="odbc:DATASOURCE";

// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pCountNames
           ( @pName varchar (3) ) AS
             DECLARE @recCount INT;
             SELECT @recCount=count(*) FROM TestTBL1 WHERE FirstName LIKE @pName;
             RETURN @recCount;";

// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
           WHERE type='P' AND name='pCountNames')
           DROP PROCEDURE pCountNames";

// Stored Procedure Call Statement
$sqlExecSP   = "{? = CALL pCountNames(?)}";

try {

    $recs=0;

    // Connect to the datasource
    $dbh = new PDO($dsn);

    // Drop existing stored procedure if exists
    $stmt = $dbh->prepare($sqlDropSP);
    $stmt->execute();

    // Create new stored procedure
    $stmt = $dbh->prepare($sqlCreateSP);
    $stmt->execute();

    // Prepare stored procedure
    $stmt = $dbh->prepare($sqlExecSP);

    // Bind parameter 1 (RETURN Value)
    $stmt->bindParam(1, $recs, PDO::PARAM_INT, 4);

    // For each letter in the parameter list
    foreach($params as $name) {

        // Bind parameter 2 for next letter
        $stmt->bindParam(2, $name['pName'], PDO::PARAM_STR);

        // Execute pCountNames stored procedure
        $stmt->execute();

        // Output Results
        printf ("\nCount of names starting with '%.1s' is %d", $name['pName'], $recs);
    }
    printf ("\n");

    // Drop stored procedure used
    $stmt = $dbh->prepare($sqlDropSP);
    $stmt->execute();

    // Close statement and data base connection
    $stmt = NULL;
    $dbh = NULL;

} catch (PDOException $e) {
   echo "Exception Occurred :" . $e->getMessage();
}
?>

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.