PHP无法检索SQL Server 2016存储过程的BEGIN CATCH ... END CATCH块中分配的[结果代码]和[输出参数]

Question

I have a simple table and a stored proc like the following:

CREATE TABLE dbo.Test
(
    TestTime DATETIME
)

INSERT INTO dbo.Test (TestTime)
VALUES('2018-12-21T13:25:45')

CREATE PROCEDURE dbo.TestProc
@pErrorMsg NVARCHAR(512) OUTPUT
AS
BEGIN
    DECLARE @result INTEGER
    SET @result = 0
    SET @pErrorMsg = N'OK';
    BEGIN TRY
        UPDATE dbo.Test
        SET TestTime = '2018-12-21T13:25:45a' -- wrong time value to raise error!
        WHERE TestTime = '2018-12-21T13:25:45'
    END TRY
    BEGIN CATCH
        SET @pErrorMsg = ERROR_MESSAGE()
        SET @result = 1
    END CATCH

    RETURN @result
END

--Test the proc, and it seems to work fine:
BEGIN
  DECLARE @res INTEGER
  SET @res = 0
  DECLARE @errMsg NVARCHAR(128)

  EXEC @res = dbo.TestProc @errMsg OUTPUT
  SELECT @res AS Result, @errMsg AS ErrorMsg
END

The stored proc can return the error code as well as the ERROR_MESSAGE() which I assign in the CATCH block.

Result | ErrorMsg

1 | Conversion failed when converting date and/or time from character string.

My php code:

$sql = "{? = call dbo.TestProc(?)}";

$res = 123;
$errorMsg = utf8_encode("abcd1234");

$paramRes = array(&$res, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT);
$paramErrorMsg = array(&$errorMsg, SQLSRV_PARAM_OUT);
$params = array($paramRes, $paramErrorMsg);

$affectedRows = 0;

$conn = sqlsrv_connect(<your_server>, <your_credential>);
$stmt = sqlsrv_query($conn, $sql, $params);
$affectedRows = sqlsrv_rows_affected($stmt);  // this works well

$nxtRes = sqlsrv_next_result($stmt);  //  This asks the sql driver to update the result code and output parameter variables

echo "</br>result code=" . strval($res) . ", error msg=" . $errorMsg . "</br>"

The issue is that if I modify the stored proc so that it can update the data successfully, my php code can capture the return code (0) and error message ("OK"), but if I just keep the stored proc as above, my php code cannot capture those things.

Could someone who had the same issue before give me some hints. Thanks. I use php7 and MS PHP driver 5.2 for SQL server.

Answer 1

I had similar problems and I suggest the following:

• always put SET NOCOUNT ON at the beginning of your stored procedures.
• fetch all result sets from sqlsrv_query(). INSERT, UPDATE or DELETE statements also return the number of affected rows as a result set (when SET NOCOUNT is OFF), not only SELECT statements.

I usually fetch results this way:

<?php

...
do {
    # Fetch data, if it's a SELECT statement
    #while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    #}
    $affected = sqlsrv_rows_affected($stmt);
} while (sqlsrv_next_result($stmt));
...
?>

I've reproduced your test case and SET NOCOUNT ON works. I hope this helps.