SQLSRV 和存储过程中的多选 [英] SQLSRV and multiple selects in Stored Procedure

问题描述

我有一个存储过程，它创建一个临时表 (#test)，用另一个表中的数据填充它，在这个临时表上运行 3 个选择并删除它.

I have a Stored Procedured which creates a temporary table (#test), fills it with data from another table, runs 3 selects on this temporal table and drops it.

原始表超过 20 GB，3 条 SELECT 语句包含原始 SP 上的许多不同条件.

The original table it's over 20 GBs big and the 3 SELECT statements contain a lot of different conditions on the original SP.

我正在使用 SQLSRV 从 PHP 执行 SP，但是我只能设法检索前 2 个结果集.

I'm executing the SP from PHP using SQLSRV however I can only manage to retrieve the 2 first result sets.

如果我从 MSSMS 运行 SP，它将正常运行并按预期返回 3 个结果集.但是从 PHP 中它只会返回 2(尝试了每种组合).

If I run the SP from MSSMS it will run just fine and returns the 3 resultsets as expected. But from PHP it will just return 2 (tried every combination).

不确定这是驱动程序的问题，sqlsrv_fetch_array 还是 sqlsrv_next_result.

Not sure if this is a problem with the driver, sqlsrv_fetch_array or sqlsrv_next_result.

SP 示例(select 语句太大，所以我将继续它们):

Example of the SP (the select statements are too big so I'll just resume them):

CREATE PROCEDURE sp_transfers
@dt date,
@campaign varchar(16)
AS
BEGIN
CREATE TABLE #test ( [column definitions...] )

BEGIN
INSERT INTO #test SELECT * FROM sometable WHERE dt = @dt AND campaign = @campaign

SELECT * FROM #test ...
SELECT * FROM #test ...
SELECT * FROM #test ...

DROP TABLE #test
END

现在从 PHP 这是我的测试代码:

Now from PHP this is my test code:

$q = sqlsrv_query($conn,"EXEC sp_transfers @dt='2013-10-01',@campaign='1234'");

sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); //OK - data as expected - 1st resultset
sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); //OK - data as expected - 2nd resultset
sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); // EMPTY

但是，如果我尝试这样做，它会起作用:

However though, if I try this it works:

sqlsrv_next_result($q);
sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); //OK - data as expected - 2nd resultset
sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); // OK - 3rd resultset shows up

这个组合也一样:

sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); //OK - data as expected - 1st resultset
sqlsrv_next_result($q);
sqlsrv_next_result($q);
var_dump(sqlsrv_fetch_array($q)); // OK - 3rd resultset shows up

我在这里做错了吗?或者有没有办法从单个 SP 中获取 3 个结果集.

Am I doing something wrong here? Or is there a way to fetch the 3 resultsets from a single SP.

提前致谢.

推荐答案

我实际上只是遇到了类似的问题并设法使以下内容起作用:

I was actually just having a similar issue and managed to get the following to work:

$result = array();

// Get return value
do {
   while ($row = sqlsrv_fetch_array($query)) {
       // Loop through each result set and add to result array
       $result[] = $row;
   }
} while (sqlsrv_next_result($query));

print_r($result);

do-while 循环将遍历所有结果(而不必手动执行此操作).似乎循环 sqlsrv_fetch_array() 是必不可少的，所以我认为这是真正的答案.

The do-while loop will advance through all results (rather than having to do this manually). It seems that looping through sqlsrv_fetch_array() is essential so I think this is the real answer.

这篇关于SQLSRV 和存储过程中的多选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！