none
Stored procedure return value

    Question

  • Is it possible to get the number that can be supplied to the RETURN statement in a stored procedure?

     

    thanks,

     

    Tom.

    Friday, July 18, 2008 1:09 PM

Answers

    • Edited by Kalman Toth Thursday, October 04, 2012 7:56 AM
    Friday, July 25, 2008 8:47 AM
  •  

    I used the following code to call a stored procedure and retrieve its return value.  The code creates a temporary stored procedure that takes two input parameters and returns their sum via the return value.  If your stored procedure executes queries, make sure you're processing all available results via sqlsrv_next_result before trying to access the return value.

     

    I hope this information proves helpful.

     

    David Sceppa

    Program Manager, SQL Server Driver for PHP

     

    Code Snippet

    $conn = sqlsrv_connect('.\SQLExpress', array('Database'=>'Northwind'));

    $sql = "CREATE PROCEDURE #ReturnParam (@p1 int, @p2 int) AS
            RETURN @p1 + @p2";
    $stmt = sqlsrv_query($conn, $sql);
        
    $sql = '{? = CALL #ReturnParam(?, ?)}';
    $returnVariable = -1;
    $inputVariable1 = 18;
    $inputVariable2 = 24;

    $params = array(array($returnVariable, SQLSRV_PARAM_OUT),
                    array($inputVariable1, SQLSRV_PARAM_IN),
                    array($inputVariable2, SQLSRV_PARAM_IN));

    $stmt = sqlsrv_query($conn, $sql, $params);

    echo "Return value: ".$returnVariable;

     

     

    Monday, July 28, 2008 5:32 PM
  • Eldis,

    Thanks again for your feedback.  The April 2009 Cumulative Update contains a fix for the issue you reported.  Here's a link to the forum post about the update.
        http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/79820348-51ad-4174-8043-515c2e3d50ef

    David Sceppa
    Program Manager - SQL Server Driver for PHP
    • Marked as answer by David Sceppa Thursday, May 14, 2009 8:26 PM
    Wednesday, April 29, 2009 8:26 PM
  • I got finally arround to updating the driver and testing the return value again and it works. Thanks!
    • Marked as answer by David Sceppa Thursday, May 14, 2009 8:25 PM
    Tuesday, May 12, 2009 10:19 AM

All replies

  •  

    That certainly looks like it should work, however it's more of a workaround. Am I to take it that this is not currently supported by the PHP driver?

    Where do I suggest this as an enhancment, or is stating it here enough?

     

    thanks,

     

    Tom.

    Friday, July 25, 2008 8:29 AM
    • Edited by Kalman Toth Thursday, October 04, 2012 7:56 AM
    Friday, July 25, 2008 8:47 AM
  •  

    I used the following code to call a stored procedure and retrieve its return value.  The code creates a temporary stored procedure that takes two input parameters and returns their sum via the return value.  If your stored procedure executes queries, make sure you're processing all available results via sqlsrv_next_result before trying to access the return value.

     

    I hope this information proves helpful.

     

    David Sceppa

    Program Manager, SQL Server Driver for PHP

     

    Code Snippet

    $conn = sqlsrv_connect('.\SQLExpress', array('Database'=>'Northwind'));

    $sql = "CREATE PROCEDURE #ReturnParam (@p1 int, @p2 int) AS
            RETURN @p1 + @p2";
    $stmt = sqlsrv_query($conn, $sql);
        
    $sql = '{? = CALL #ReturnParam(?, ?)}';
    $returnVariable = -1;
    $inputVariable1 = 18;
    $inputVariable2 = 24;

    $params = array(array($returnVariable, SQLSRV_PARAM_OUT),
                    array($inputVariable1, SQLSRV_PARAM_IN),
                    array($inputVariable2, SQLSRV_PARAM_IN));

    $stmt = sqlsrv_query($conn, $sql, $params);

    echo "Return value: ".$returnVariable;

     

     

    Monday, July 28, 2008 5:32 PM
  • Hey,I've been working on this return value issue for a day now, and it works fine when my I only return the return value but if I also select something(anything) it will cause a php access violation.
    The problem here is that it will work if I remove the Select statement but will crash my web server if it's there. Running PHP 5.2.5 and ms sqlserver 2000

    Example procedure:
    Code Snippet

    CREATE PROCEDURE [dbo].[usp_Test]
        @a bit
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT 199
        IF @a=0
            RETURN 11
        ELSE
            RETURN 22
    END


    Example procedure call:

    function testquery() {

    $tsql = '{? = CALL usp_Test(?)}';
    $returnVariable = -1;
    $inputVariable1 = 0;

    $params = array(array($returnVariable, SQLSRV_PARAM_OUT),
    array($inputVariable1, SQLSRV_PARAM_IN));
    $stmt = sqlsrv_query($this->conn, $tsql, $params);
    $next_result = sqlsrv_next_result($statement);

    echo $next_result;


    }



    Friday, October 31, 2008 8:23 AM
  • Eldis-

     

    I *think* I was able to reproduce this issue (I'm running PHP 5.2.6 and SQL Server 2005). I modified your code slightly (below) and found that the PHP process crashed, not the web server (I tried on IIS 7.0 and IIS 5.1). Can you confirm that the web server crashed and not the PHP process? (What errors are you seeing?)

     

    In any case, I've talked with the development team and the issue is being looked into.

     

    Thanks for bringing this to our attention.

     

    -Brian

     

    Code Snippet

     

    $tsql = '{? = CALL TestProc2(?)}';
    $returnVariable = -1;
    $inputVariable1 = 0;

    $params = array(
      array($returnVariable, SQLSRV_PARAM_OUT),
      array($inputVariable1, SQLSRV_PARAM_IN)
      );
    $stmt = sqlsrv_query($conn, $tsql, $params);
    if($stmt === false)
    {
     print_r(sqlsrv_errors());
    }

    print_r(sqlsrv_fetch_array($stmt));

    $next_result = sqlsrv_next_result($stmt);

    print_r(sqlsrv_fetch_array($stmt));

     

     

     

    Friday, October 31, 2008 7:55 PM
  • We're still investigating the issue to determine the cause.

     

    In the meantime, the best workaround I can suggest is to return the return value via a SELECT statement.  Changing the code above to the following worked for me:

     

    Code Snippet

    //Create the temporary stored procedure
    $tsql = 'CREATE PROCEDURE #usp_Test (@a int)
             AS
             BEGIN
                 SET NOCOUNT ON;
                 SELECT 199
                 IF @a=0
                     SELECT 11
                 ELSE
                     SELECT 22
             END';
    $stmt = sqlsrv_query($conn, $tsql);

     

    //Call the stored procedure
    $tsql = '{CALL #usp_Test(?)}';
    $inputVariable1 = 0;
    $params = array(array($inputVariable1, SQLSRV_PARAM_IN));
    $stmt = sqlsrv_query($conn, $tsql, $params);

     

    //Retrieve data from the first resultset
    print_r(sqlsrv_fetch_array($stmt));

     

    //Move to the second resultset
    $next_result = sqlsrv_next_result($stmt);

     

    //Retrieve data from the second resultset
    print_r(sqlsrv_fetch_array($stmt));

     

     

    David Sceppa

    Program Manager - Microsoft SQL Server Driver for PHP

    • Proposed as answer by David Sceppa Monday, March 09, 2009 6:57 PM
    Monday, November 03, 2008 11:55 PM
  • We've been using Select as a replacement to return value but we are still eager to start using the actual return value as soon as it's possible.

    Any idea if a fix for this will be available in the next release ?
    Monday, December 01, 2008 9:12 AM
  • Eldis,

    We are working on a fix for this issue as part of an upcoming cumulative update for the extension.  Keep an eye on our team blog for more information.  Thanks.

    David Sceppa
    Program Manager - SQL Server Driver for PHP

    Monday, March 09, 2009 6:56 PM
  • Eldis,

    Thanks again for your feedback.  The April 2009 Cumulative Update contains a fix for the issue you reported.  Here's a link to the forum post about the update.
        http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/79820348-51ad-4174-8043-515c2e3d50ef

    David Sceppa
    Program Manager - SQL Server Driver for PHP
    • Marked as answer by David Sceppa Thursday, May 14, 2009 8:26 PM
    Wednesday, April 29, 2009 8:26 PM
  • What does the April Update do fix here? Is it possible now to get a *real* return value of a stored procedure?

    We used this select-return value workaround all the time in the old mssql-php driver and I hoped for some better way with the new driver like you can do it with the adodb-interface.

    Cheers,
    Andreas

    Tuesday, May 05, 2009 7:56 PM
  • I got finally arround to updating the driver and testing the return value again and it works. Thanks!
    • Marked as answer by David Sceppa Thursday, May 14, 2009 8:25 PM
    Tuesday, May 12, 2009 10:19 AM