Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

How do I retrieve output parameter values from SQL Server stored procedures by using PHP ODBC?

A SQL Server stored procedure can return information by using either a RETURN statement or one or more output parameters. A RETURN statement enables a stored procedure to return an integer value, which reports the execution status of the procedure. Output parameters enable procedures to return other types of values. For example, character strings.

PHP ODBC, the PHP extension that enables PHP to access databases for which an ODBC driver is available, does not provide direct support for output parameters. This article describes a workaround in which two stored procedures are used. The first procedure, which is not called from PHP, returns information by using output parameters. The second procedure, which is called from PHP, calls the first procedure and converts the output parameter values into a result set.

Note PHP Data Objects (PDO), which also enables PHP to access databases for which an ODBC driver is available, does provide direct support for output parameters.

The example PHP scripts in this article use this workaround to return a single output parameter value and multiple output parameter values. To run the sample PHP scripts, you need:

  1. A SQL Server database in which you have permission to create stored procedures.
  2. A SQL Server ODBC driver.

    Easysoft provide a SQL Server ODBC driver for Linux and UNIX platforms:

    1. Download the SQL Server ODBC driver for the Linux or UNIX platform on which PHP is installed.
    2. Install, license, and test the SQL Server ODBC driver on this machine.

      To test the SQL Server ODBC driver, create an ODBC data source that connects to the SQL Server database where you have permission to create procedures.

      For more information, refer to the SQL Server ODBC driver documentation and Getting Started Guide.

<?php
/* =====================================================================
How to retrieve a single output parameter value from a SQL Server stored
procedure by using PHP ODBC.
======================================================= */

/* Do not display ODBC warning messages. */
error_reporting( E_ERROR | E_PARSE );


/* Replace myDSN with the name of your ODBC data source.
Replace myUser and myPassword with the SQL Server database user name
and password. */
$dataSource = "myDSN";
$user = "myUser";
$password = "myPassword";
$conn = odbc_connect( $dataSource, $user, $password);
if( !$conn )
{
     if( phpversion() < "4.0" )
     {
          exit( "Connection Failed: . $php_errormsg" );
     }
     else
     {
          exit( "Connection Failed:" . odbc_errormsg() );
     }
}


/* This stored procedure accepts one input parameter, a number,
which is then squared and returned in an output parameter. */
$ret = odbc_exec( $conn, "CREATE PROCEDURE InputSquared
                          @InputVal integer,
                          @OutputVal integer OUTPUT
                          AS
                          SELECT @OutputVal=@InputVal*@InputVal" );


if( $ret )
{

     /* To retrieve the output parameter value via PHP ODBC, a second procedure is
     is required. This procedure calls InputSquared and converts the output parameter
     value returned into a result set. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputSquared @InputVal integer
                        AS
                        DECLARE @OutputVal integer
                        EXEC InputSquared @InputVal, @OutputVal OUTPUT
                        SELECT @OutputVal AS OutputValue" );

     /* Call the intermediary procedure from PHP. */
     $stmt = odbc_prepare( $conn, "{call ReturnInputSquared( ? )}" );
     $i = 2;
     odbc_execute( $stmt, array($i) );
     $result = odbc_result_all( $stmt );
     odbc_exec( $conn, "DROP PROCEDURE InputSquared, ReturnInputSquared" );
}


/* $dataSource needs to connect to a SQL Server database in which you have
permission to create a stored procedure. */
else
{
     print( "Unable to create stored procedure:" . odbc_errormsg() );
}


// Disconnect the database from the database handle.
odbc_close( $conn );
?>

<?php
/* =====================================================================
How to retrieve multiple output parameter values from a SQL Server
stored procedure by using PHP ODBC.
======================================================= */

/* Do not display ODBC warning messages. */
error_reporting( E_ERROR | E_PARSE );

/* Replace myDSN with the name of your ODBC data source.
Replace myUser and myPassword with the SQL Server database user name and
password. */
$dataSource = "myDSN";
$user = "myUser";
$password = "myPassword";
$conn = odbc_connect( $dataSource, $user, $password);
if( !$conn )
{
     if( phpversion() < "4.0" )
     {
          exit( "Connection Failed: . $php_errormsg" );
     }
     else
     {
          exit( "Connection Failed:" . odbc_errormsg() );
     }
}

/* This stored procedure accepts one input parameter, a number,
which is then squared, cubed and returned in two output parameters. */
$ret = odbc_exec( $conn, "CREATE PROCEDURE InputToPowerOf
                          @InputVal integer,
                          @SquaredOutputVal integer OUTPUT,
                          @CubedOutputVal integer OUTPUT
                          AS
                          SELECT @SquaredOutputVal=@InputVal*@InputVal
                          SELECT @CubedOutputVal=@InputVal*@InputVal*@InputVal" );

if( $ret )
{

     /* To retrieve the output parameter value via PHP ODBC, a second procedure is
     is required. This procedure calls ReturnInputToPowerOf and converts the output
     parameters values returned into a result set. The result set is a single row with
     multiple fields. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputToPowerOf
                        @InputVal integer
                        AS
                        DECLARE @SquaredOutputVal integer
                        DECLARE @CubedOutputVal integer
                        EXEC InputToPowerOf @InputVal, @SquaredOutputVal OUTPUT, @CubedOutputVal OUTPUT
                        SELECT @SquaredOutputVal, @CubedOutputVal" );

     $stmt = odbc_prepare( $conn, "{call ReturnInputToPowerOf( ? )}" );
     $i = 2;
     odbc_execute( $stmt, array($i) );

     print "Single result set:\n";
     $result = odbc_result_all( $stmt );

     odbc_exec( $conn, "DROP PROCEDURE ReturnInputToPowerOf" );

     /* This procedure calls ReturnInputToPowerOf and converts the output
     parameters values returned into multiple result sets. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputToPowerOf
                        @InputVal integer
                        AS
                        DECLARE @SquaredOutputVal integer
                        DECLARE @CubedOutputVal integer
                        EXEC InputToPowerOf @InputVal, @SquaredOutputVal OUTPUT, @CubedOutputVal OUTPUT
                        SELECT @SquaredOutputVal
                        SELECT @CubedOutputVal" );

     $stmt = odbc_prepare( $conn, "{call ReturnInputToPowerOf( ? )}" );
     odbc_execute( $stmt, array($i) );

     print "Multiple result sets:\n";

     do
     {
          $result = odbc_result_all( $stmt );
     } while ( odbc_next_result( $stmt ) );

     odbc_exec( $conn, "DROP PROCEDURE InputToPowerOf, ReturnInputToPowerOf" );
}

/* $dataSource needs to connect to a SQL Server database in which you have
permission to create a stored procedure. */
else
{
     print( "Unable to create stored procedure:" . odbc_errormsg() );
}

// Disconnect the database from the database handle.
odbc_close( $conn );
?>