使用sqlsrv调用具有多个结果集的存储过程-无错误接收

Question

如果我试图用PHP Driver for SQL Server(sqlsrv扩展(在PHP中调用具有多个结果集的存储过程，我会得到结果集，直到出现错误，但没有收到错误。下面是一个简短的例子：

存储过程：

CREATE PROCEDURE dbo.pr_testmultipleresult
AS
BEGIN
SET NOCOUNT ON;
SELECT 1 AS result1;
SELECT 2 AS result2;
RAISERROR(
'This is an error' -- Message
,16                 -- Severity,  
,1                  -- State
);
SELECT 3 AS result3;
END;
GO

PHP代码：

<?php
$serverName = "localhostSQL2012TEST";
$connectionInfo = array("Database" => "TEST", "UID" => "", "PWD" => "");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$query = sqlsrv_query($conn, '{call dbo.pr_testmultipleresult}');
if ($query === false) {
die(print_r(sqlsrv_errors(), true));
}
do {
while ($row = sqlsrv_fetch_array($query)) {
// Loop through each result set and add to result array
$result[] = $row;
}
} while (sqlsrv_next_result($query));
echo "<pre>";
var_dump($result);
echo "</pre>";

结果：

array(2) {
[0]=>
array(2) {
[0]=>
int(1)
["result1"]=>
int(1)
}
[1]=>
array(2) {
[0]=>
int(2)
["result2"]=>
int(2)
}
}

如果在第一条select语句之前调用raiserror，那么sqlsrv中会出现错误。但是，如果在raiserror之前只有一个select，那么我在sqlsrv中不会得到它。

有没有我必须添加的配置才能得到错误？

Answer 1

解释：

当存储过程返回多个结果集时，您需要检查sqlsrv_query()调用的结果和每个sqlsrv_next_result()调用的结果是否存在错误：

<?php
...
# T-SQL
$result = array();
$rc = 0;
$params = array(
array(&$rc, SQLSRV_PARAM_OUT)
);
$query = sqlsrv_query($conn, 'EXEC ? = dbo.pr_testmultipleresult', $params);
if ($query === false) {
echo "Error executing stored procedure.".print_r(sqlsrv_errors(), true)."<br>";
} else {
while ($row = sqlsrv_fetch_array($query)) {
$result[] = $row;
}
if (sqlsrv_next_result($query) === false) {
echo "Error fetching a result set.".print_r(sqlsrv_errors(), true)."<br>";
} else {
while ($row = sqlsrv_fetch_array($query)) {
$result[] = $row;
}
}   
if (sqlsrv_next_result($query) === false) {
echo "Error fetching a result set.".print_r(sqlsrv_errors(), true)."<br>";
} else {
while ($row = sqlsrv_fetch_array($query)) {
$result[] = $row;
}
}   
echo "<pre>";
var_dump($result);
echo "</pre>";  
sqlsrv_free_stmt($query);
}
# End
sqlsrv_close($conn);
?>

结果：

Error fetching a result set.Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 50000 [code] => 50000 [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]This is an error [message] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]This is an error ) )
array(2) {
[0]=>
array(2) {
[0]=>
int(1)
["result1"]=>
int(1)
}
[1]=>
array(2) {
[0]=>
int(2)
["result2"]=>
int(2)
}
}

附带说明一下，配置错误和警告处理的一个可用选项是sqlsrv_configure()函数，但我认为它在这种特定情况下没有用处。

可选解决方案：

我建议使用以下方法，这是解决您问题的可能方法(至少我能够执行一个过程，获得所有可能的结果集并获得结果状态代码(。

您需要更改存储过程并使用RETURN无条件退出该过程并返回适当的状态。您还需要更改PHP脚本中的语句，并使用带有输出参数的EXEC ? = dbo.pr_testmultipleresult语句来检查过程返回的状态。注意，sqlsrv_query()返回语句执行的结果，而不是存储过程执行的结果。

存储过程：

ALTER PROCEDURE dbo.pr_testmultipleresult
AS
BEGIN
SET NOCOUNT ON;
DECLARE @return int
SET @return = 0
SELECT 1 AS result1;
BEGIN TRY  
SELECT 1 / 0 AS Result2
END TRY  
BEGIN CATCH  
SET @return = -1
END CATCH 
SELECT 3 AS result3;
RETURN @return
END;

PHP:

<?php
$server = 'serverinstance';
$database = 'database';
$uid = 'username';
$pwd = 'password';
# Connection
$cinfo = array("Database" => $database, "UID" => $uid, "PWD" => $pwd);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
# T-SQL
$result = array();
$rc = 0;
$params = array(
array(&$rc, SQLSRV_PARAM_OUT)
);
$query = sqlsrv_query($conn, 'EXEC ? = dbo.pr_testmultipleresult', $params);
if ($query === false) {
die(print_r(sqlsrv_errors(), true));
}
do {
while ($row = sqlsrv_fetch_array($query)) {
// Loop through each result set and add to result array
$result[] = $row;
}
} while (sqlsrv_next_result($query));
# Check result
if ($rc < 0) {
echo "Result from stored procedure: ERROR";
} else {
echo "Result from stored procedure: OK";
}   
echo "<br>"."Results:"."<br>";
echo print_r($result, true);
# End
sqlsrv_free_stmt($query);
sqlsrv_close($conn);
?>