Symfony中Doctrine执行MSSQL原生SQL报SQLSTATE[IMSSP]无字段错误

我在Symfony框架中使用Doctrine执行包含临时表声明、数据插入及最终查询的原生MSSQL语句，该SQL在HeidiSQL中可正常运行，但通过以下Symfony代码执行时，报错SQLSTATE[IMSSP]: The active result for the query contains no fields.

执行的SQL语句： #

DECLARE @ebayitems TABLE (kartikel INT, cartnr VARCHAR(255), ebayPrice DECIMAL(10,2), ebayID VARCHAR(255))
DECLARE @amazonitems TABLE (kartikel INT, cartnr VARCHAR(255), amazonPrice DECIMAL(10,2), amazonPriceVersand DECIMAL(10,2), amazonASIN VARCHAR(255))
DECLARE @shopitems TABLE (kartikel INT, cartnr VARCHAR(255), ebayPrice DECIMAL(10,2), latestEbayID VARCHAR(255), amazonPrice DECIMAL(10,2), latestAmazonASIN VARCHAR(255))

INSERT INTO @ebayitems
select tArt.kartikel, tArt.cartnr, ebay.ebay_price AS ebayPrice, ebay.ItemID AS ebayID
...
GROUP BY tArt.kartikel, tArt.cartnr, ebay.ebay_price, ebay.ItemID
                
INSERT INTO @amazonitems                
select tArt.kartikel, tArt.cartnr, amazon.fprice, CASE WHEN amazon.fprice < 315 THEN amazon.fprice + 4.90 ELSE amazon.fprice + 5.90 end AS amazonPrice, amazon.casin1 AS AmazonASIN
...
GROUP BY tArt.kartikel, tArt.cartnr, amazon.fprice, amazon.casin1                              

INSERT INTO @shopitems
SELECT ebayitems.kartikel, ebayitems.cartnr, ebayPrice, MAX(ebayID) AS latestEbayID, amazonitems.amazonPriceVersand, max(amazonitems.amazonASIN) AS latestAmazonASIN 
... 
GROUP BY ebayitems.kartikel, ebayitems.cartnr, ebayPrice, amazonitems.amazonPriceVersand

SELECT items.cartnr, 
            tPreis.kpreis AS kpreis,
            (CASE WHEN ebayPrice > amazonPrice THEN (FLOOR(MIN(amazonPrice) * 0.95)-0.10) / 1.19 
                            ELSE (FLOOR(MIN(ebayPrice) * 0.95)-0.10) / 1.19 END) AS shoppreis, 
            ebayprice, 
            latestEbayID, 
            amazonprice, 
            latestAmazonASIN
...
GROUP BY items.cartnr, kpreis, ebayprice, 
            latestEbayID, 
            amazonprice, 
            latestAmazonASIN

报错的Symfony代码： #

$items = $conn->executeQuery($selectSQL);

var_dump($items->fetchAllAssociative());

解决方案 #

方法1：在SQL开头添加SET NOCOUNT ON;

MSSQL执行INSERT等语句时，默认会返回受影响行数的结果集，Doctrine会优先处理这些中间结果集，导致最后真正的SELECT结果被忽略。添加SET NOCOUNT ON;可以抑制这些中间结果输出，让Doctrine只处理最终的查询结果。

修改后的SQL开头：

SET NOCOUNT ON;
DECLARE @ebayitems TABLE (kartikel INT, cartnr VARCHAR(255), ebayPrice DECIMAL(10,2), ebayID VARCHAR(255))
-- 后续SQL内容保持不变

方法2：启用多结果集处理

告诉Doctrine当前SQL包含多个结果集，手动遍历到最后一个有效结果集：

$stmt = $conn->executeQuery($selectSQL, [], ['multiple' => true]);

// 跳过所有中间空结果集，获取最后一个有效结果
do {
    $result = $stmt->fetchAllAssociative();
} while ($stmt->nextRowset());

var_dump($result);

方法3：封装为存储过程

将整个SQL逻辑封装成MSSQL存储过程，调用存储过程时只会返回最终的查询结果：

1. 创建存储过程：

CREATE PROCEDURE GetShopItems
AS
BEGIN
    SET NOCOUNT ON;
    -- 这里放入原SQL的所有逻辑
    DECLARE @ebayitems TABLE (kartikel INT, cartnr VARCHAR(255), ebayPrice DECIMAL(10,2), ebayID VARCHAR(255))
    INSERT INTO @ebayitems ...
    -- 原SQL中的所有插入、临时表操作
    -- 最终的SELECT语句
    SELECT items.cartnr, ... 
END

2. 在Symfony中调用存储过程：

$stmt = $conn->executeQuery('EXEC GetShopItems');
var_dump($stmt->fetchAllAssociative());

内容的提问来源于stack exchange，提问作者Aurel Dragut