I want to call a stored procedure in my Laravel project. I am using a MSSQL Azure DB. For example this one: click here

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

I want to return a data table, a return value (int) and an outputreference (xml).

DB::select() and inserting the query does not work for me. I won't get the return value and outputreference back.

I tried this click here

Unfortunately I get an error saying:
The $OutputReference and $ReturnValue is undefined.

public function testSP()
    {
        try {
            // Roep de stored procedure aan om de resultaten op te halen
            $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)) {
                // Haal de XML-response en returnwaarde op
                $xmlResponse = $results[0]->OutputReference;
                $returnValue = $results[0]->ReturnValue;

                // Doe iets met de XML-response en returnwaarde
                return response()->json([
                    'message' => 'Stored procedure uitgevoerd',
                    'xmlResponse' => $xmlResponse,
                    'returnValue' => $returnValue,
                ]);
            } else {
                // Geen resultaten ontvangen van de stored procedure
                return response()->json(['error' => 'Geen resultaten ontvangen']);
            }
        } catch (QueryException $e) {
            // Als een databasefout optreedt, genereer een aangepaste foutmelding
            return response()->json(['error' => 'Databasefout']);
        }
    }