Laravel调用MSSQL Azure存储过程获取返回值及输出参数问题

我在Laravel项目中使用Azure MSSQL数据库，需要调用名为sp_GetAllShopInfo的存储过程。该存储过程会返回数据表、int类型返回值，以及xml类型的OutputReference输出参数。尝试用DB::select()执行查询时无法获取返回值和输出参数，使用以下代码时还出现了“$OutputReference和$ReturnValue未定义”的错误：

原存储过程执行语句（SQL） #

DECLARE @return_value int, @OutputReference xml
EXEC @return_value = [sp_GetAllShopInfo] @OutputReference = @OutputReference OUTPUT
SELECT @OutputReference as N'@OutputReference' SELECT 'Return Value' = @return_value
GO

尝试的Laravel代码 #

public function testSP()
{
    try {
        // 调用存储过程获取结果
        $results = DB::select('
        DECLARE @return_value int;
        DECLARE @OutputReference xml;

        EXEC @return_value = [sp_GetAllShopInfo]
            @OutputReference = @OutputReference OUTPUT;

        SELECT @OutputReference as OutputReference, @return_value as ReturnValue;
    ');

        if (!empty($results)) {
            // 获取XML响应和返回值
            $xmlResponse = $results[0]->OutputReference;
            $returnValue = $results[0]->ReturnValue;

            // 处理XML响应和返回值
            return response()->json([
                'message' => 'Stored procedure uitgevoerd',
                'xmlResponse' => $xmlResponse,
                'returnValue' => $returnValue,
            ]);
        } else {
            // 未获取到存储过程结果
            return response()->json(['error' => 'Geen resultaten ontvangen']);
        }
    } catch (QueryException $e) {
        // 数据库错误处理
        return response()->json(['error' => 'Databasefout']);
    }
}

解决方案 #

Laravel的DB::select()在处理带输出参数、返回值以及多结果集的存储过程时存在局限，建议直接使用PDO原生操作来处理：

public function testSP()
{
    try {
        // 获取PDO连接实例
        $pdo = DB::connection()->getPdo();
        
        // 准备存储过程调用语句
        $stmt = $pdo->prepare("EXEC ? = [sp_GetAllShopInfo] @OutputReference = ? OUTPUT");
        
        // 绑定返回值（参数1，int类型输入输出）
        $returnValue = 0;
        $stmt->bindParam(1, $returnValue, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT);
        
        // 绑定XML输出参数（参数2，大对象类型，指定UTF8编码）
        $outputXml = null;
        $stmt->bindParam(2, $outputXml, PDO::PARAM_LOB | PDO::PARAM_INPUT_OUTPUT, 0, PDO::SQLSRV_ENCODING_UTF8);
        
        // 执行存储过程
        $stmt->execute();
        
        // 获取存储过程返回的数据表结果
        $shopInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 切换到下一个结果集（跳过执行状态结果）
        $stmt->nextRowset();
        
        return response()->json([
            'message' => '存储过程执行成功',
            'shopInfo' => $shopInfo,
            'xmlResponse' => $outputXml,
            'returnValue' => $returnValue,
        ]);
        
    } catch (\Exception $e) {
        return response()->json(['error' => $e->getMessage()]);
    }
}

关键说明 #

• 使用PDO原生的bindParam方法绑定输入输出参数，明确指定参数类型和编码，确保XML参数能正确读取
• 执行后先读取存储过程返回的数据表结果，再通过nextRowset()切换结果集，保证输出参数和返回值被正确赋值
• 异常捕获改为\Exception，可以获取更详细的错误信息，便于排查问题

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