Функция выполняется медленно (12 с) в PHP, но быстро (до секунды) в SSMS. Каковы некоторые области расследования?

У меня есть функция SQL Server, которая использует несколько CTE WITH для предварительной обработки некоторых данных перед выводом в формате FOR JSON PATH.

Запрос выполняется в SSMS менее секунды, но при вызове из нашего тестового веб-приложения PHP занимает двенадцать секунд.

Мне известно о «обнюхивании параметров» для хранимых процедур, но мне неясно, то же самое ли это для функций, и если да, то как решить проблему. В хранимых процедурах использование локальных переменных улучшает проблемы с анализом параметров, но мне не известно о подобном исправлении для функций.

Где еще можно найти хорошую отправную точку для исследования такого вопроса?

Как указано ниже, PHP-код выглядит следующим образом:

include '../connections/iconnection.inc';
$inputJSON = trim(file_get_contents("php://input"));
$decodedJSON = (json_decode($inputJSON, true));
$SQLStmt = "SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (?,?,?,?,?,?,?,?,?,?,?);";
$SQLPrms=array();
(!empty($decodedJSON['siteid']) ? array_push($SQLPrms,$decodedJSON['siteid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['contractorid']) ? array_push($SQLPrms,$decodedJSON['contractorid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['containerid']) ? array_push($SQLPrms,$decodedJSON['containerid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastetypeid']) ? array_push($SQLPrms,$decodedJSON['wastetypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['containersizeid']) ? array_push($SQLPrms,$decodedJSON['containersizeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['productcategoryid']) ? array_push($SQLPrms,$decodedJSON['productcategoryid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['producttypeid']) ? array_push($SQLPrms,$decodedJSON['producttypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['productid']) ? array_push($SQLPrms,$decodedJSON['productid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['serviceid']) ? array_push($SQLPrms,$decodedJSON['serviceid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastesubtypeid']) ? array_push($SQLPrms,$decodedJSON['wastesubtypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastesubtypeqty']) ? array_push($SQLPrms,$decodedJSON['wastesubtypeqty']) : array_push($SQLPrms,NULL));
$RS_Result01 = sqlsrv_query($conn01,$SQLStmt,$SQLPrms);
if ($RS_Result01 === false) {
    $_SESSION['error-url'] = basename(__FILE__);
    $_SESSION['error-line'] = __LINE__;
    $_SESSION['error-sqlmsg'] = sqlsrv_errors();
    $_SESSION['error-sqlstmt'] = $SQLStmt;
    $_SESSION['error-sqlprms'] = $SQLPrms;
    header("Location: "."error.php");
    exit();
}
$ROW_Result01 = sqlsrv_fetch_array($RS_Result01);

Обновлено: добавление OPTION (RECOMPILE) к исходному вызову следующим образом:

$SQLStmt = "SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (?,?,?,?,?,?,?,?,?,?,?) OPTION (RECOMPILE);";

привело к тому, что время отклика функции в среде разработки составило менее секунды. Я впервые использую OPTION (RECOMPILE) и осознаю, что это не идеальное решение этой проблемы. Есть ли лучший способ обеспечить постоянную хорошую практику?

Определение функции следующее:

USE [upgradeverify]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_JSON_QueryPricingForOrder_v5.0]    Script Date: 07/07/2024 14:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION 
    [dbo].[udf_JSON_QueryPricingForOrder_v5.0]
    (
        @SiteId INT,
        @ContractorId INT = NULL,
        @ContainerId INT = NULL,
        @WasteTypeId INT = NULL,
        @ContainerSizeId INT = NULL,
        @ProductCategoryId INT = NULL,
        @ProductTypeId INT = NULL,
        @ProductId INT = NULL,
        @ServiceId INT = NULL,
        @WasteSubTypeId VARCHAR(300) = NULL,
        @WasteSubTypeQty VARCHAR(300) = NULL
    )

RETURNS TABLE
AS RETURN
(
-- Create CTE of child Client records

    WITH 
        /************************************************************************************************************************/
        /****** Create ClientList - list of all parent to this site                                                        ******/
        /************************************************************************************************************************/
        ClientList AS
            (
                SELECT
                    ClientHierarchy.ParentClientId,
                    ClientHierarchy.ChildClientId,
                    ClientHierarchy.Level,
                    Client.*
                FROM
                    ClientHierarchy
                INNER JOIN 
                    Client ON Client.ClientId = ClientHierarchy.ChildclientId
                WHERE
                    (
                        (ChildClientId = (SELECT ClientId FROM Site WHERE Site.SiteId = @SiteId)) -- define the starting point
                    )
                UNION ALL
                SELECT
                    ClientHierarchy.ParentClientId,
                    ClientHierarchy.ChildClientId,
                    ClientHierarchy.Level,
                    Client.*
                FROM
                    ClientHierarchy
                INNER JOIN 
                    Client ON Client.ClientId = ClientHierarchy.ChildclientId
                INNER JOIN
                    ClientList ON ClientHierarchy.ChildClientId = ClientList.ParentclientId
            ),

        /************************************************************************************************************************/
        /****** Create Markup - Retrieval of markup feilds from BaselinePriceMarkup                                        ******/
        /************************************************************************************************************************/

        Markup AS
            (
                SELECT
                    SalePriceMarkup,
                    RetailPriceMarkup
                FROM
                    BaselinePriceMarkup
            ),

        /************************************************************************************************************************/
        /****** Split the data input strings into CTE                                                                      ******/
        /************************************************************************************************************************/

        SplitWasteSubTypeId AS
            (
                SELECT 
                    value AS WasteSubTypeId,
                    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
                FROM STRING_SPLIT(@WasteSubTypeId, ',')
            ),

        SplitWasteSubTypeQty AS
            (
                SELECT 
                    value AS WasteSubTypeQty,
                    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
                FROM STRING_SPLIT(@WasteSubTypeQty, ',')
            ),

        /************************************************************************************************************************/
        /****** Cretae the Breakdown CTE from the split CTEs                                                               ******/
        /************************************************************************************************************************/

        Breakdown AS
            (
                SELECT 
                    s1.WasteSubTypeId,
                    s2.WasteSubTypeQty
                FROM SplitWasteSubTypeId s1
                LEFT OUTER JOIN SplitWasteSubTypeQty s2
                ON s1.row_num = s2.row_num
            ),

        /************************************************************************************************************************/
        /****** Create PriceList - Retrieval of raw data from ContractorPricing and BaselinePricing                        ******/
        /****** This will only Retrieve baseline prices for any client With Reseller = 1 Of Reseller is 0 then             ******/
        /****** ContractorPrice is returned                                                                                ******/
        /************************************************************************************************************************/

        Pricelist AS
            (
                SELECT
                CASE 
                    WHEN
                        ContainerID IS NOT NULL
                    THEN
                        ROW_NUMBER() OVER (PARTITION BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId ORDER BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId,Level DESC)
                    WHEN
                        ProductCategoryId IS NOT NULL
                    THEN
                        ROW_NUMBER() OVER (PARTITION BY ContractorId,ProductCategoryId,ProductTypeId,ProductId ORDER BY ContractorId,ProductCategoryId,ProductTypeId,ProductId,Level DESC)
                    WHEN
                        ServiceId IS NOT NULL
                    THEN
                        ROW_NUMBER() OVER (PARTITION BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId ORDER BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId,Level DESC)
                END AS RowNumber,
                *
                FROM
                    (
                        SELECT
                            ContractorPrice.ClientId,
                            ContractorPrice.SiteId,
                            CASE
                                WHEN ClientList.Level IS NULL
                            THEN 
                                40
                            ELSE
                                ClientList.Level
                            END AS Level,
                            0 AS BaselinePrice,
                            CASE
                                WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
                            THEN 
                                1
                            ELSE
                                0
                            END AS Reseller,
                            ContractorPrice.Reference,
                            ContractorPrice.ContractNotes,
                            ContractorPrice.ContractorId,
                            Contractor.ContractorName,
                            Contractor.Reference AS ContractorReference,
                            Contractor.AddrLine1 + ', ' + 
                            CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2 <> '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3 <> '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4 <> '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END + 
                            Contractor.AddrCity + ', ' + 
                            Contractor.AddrCounty + ', ' + 
                            Contractor.AddrPostcode AS ContractorAddress, 
                            Contractor.ContractorName + ', ' + 
                            Contractor.AddrLine1 + ', ' + 
                            CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2 <> '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3 <> '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4 <> '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END + 
                            Contractor.AddrCity + ', ' + 
                            Contractor.AddrCounty + ', ' + 
                            Contractor.AddrPostcode AS ContractorFullAddress, 
                            ContractorPrice.ContainerId,
                            ContractorPrice.WasteTypeId,
                            ContractorPrice.ContainerSizeId,
                            ContractorPrice.ProductCategoryId,
                            ContractorPrice.ProductTypeId,
                            ContractorPrice.ProductId,
                            ContractorPrice.ServiceId,
                            ContractorPrice.WasteSubTypeId,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
                            ELSE
                                CAST(CostHaulage AS NUMERIC(8,2))
                            END AS CostHaulage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
                            ELSE
                                CAST(CostTonnage AS NUMERIC(8,2))
                            END AS CostTonnage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(SaleHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
                            ELSE
                                CAST(SaleHaulage AS NUMERIC(8,2))
                            END AS SaleHaulage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(SaleTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
                            ELSE
                                CAST(SaleTonnage AS NUMERIC(8,2))
                            END AS SaleTonnage,
                            IncludedTonnage
                        FROM
                            ContractorPrice
                        INNER JOIN  
                            Contractor ON Contractor.ContractorId = ContractorPrice.ContractorId
                        LEFT OUTER JOIN
                            ClientList ON ClientList.ClientId = ContractorPrice.ClientId
                        WHERE
                            (
                                (
                                    (ContractorPrice.SiteId = @SiteId) OR
                                    (ContractorPrice.ClientId IN (SELECT ClientId FROM ClientList))
                                ) AND
                                (
                                    (
                                        (ContractorPrice.ContractorId = @ContractorId) AND
                                        (@ContractorId IS NOT NULL)
                                    ) OR
                                    (@ContractorId IS NULL)
                                ) AND
                                (Contractor.Active = 1) AND
                                (
                                    (
                                        (ContractorPrice.ContainerId = @ContainerId) AND
                                        (@ContainerId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.ContainerSizeId = @ContainerSizeId) AND
                                        (@ContainerSizeId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (ContractorPrice.ProductCategoryId = @ProductCategoryId) AND
                                        (@ProductCategoryId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.ProductTypeId = @ProductTypeId) AND
                                        (@ProductTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.ProductId = @ProductId) AND
                                        (@ProductId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (ContractorPrice.ServiceId = @ServiceId) AND
                                        (@ServiceId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
                                        (@WasteSubTypeId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (ContractorPrice.ServiceId = @ServiceId) AND
                                        (@ServiceId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (ContractorPrice.WasteSubTypeId IS NULL)
                                    ) 
                                )
                            )
                        UNION

                        SELECT
                            NULL AS ClientId,
                            SiteId,
                            50 AS Level,
                            1 As BaselinePrice,
                            CASE
                                WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
                            THEN 
                                1
                            ELSE
                                0
                            END AS Reseller,
                            BaseLinePriceHeader.Reference AS Reference,
                            'Baseline Price agreed with contractor' AS ContractNotes,
                            BaseLinePriceHeader.ContractorId,
                            Contractor.ContractorName,
                            Contractor.Reference AS ContractorReference,
                            Contractor.AddrLine1 + ', ' + 
                            CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2 <> '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3 <> '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4 <> '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END + 
                            Contractor.AddrCity + ', ' + 
                            Contractor.AddrCounty + ', ' + 
                            Contractor.AddrPostcode AS ContractorAddress, 
                            Contractor.ContractorName + ', ' + 
                            Contractor.AddrLine1 + ', ' + 
                            CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2 <> '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3 <> '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END + 
                            CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4 <> '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END + 
                            Contractor.AddrCity + ', ' + 
                            Contractor.AddrCounty + ', ' + 
                            Contractor.AddrPostcode AS ContractorFullAddress, 
                            BaseLinePriceDetail.ContainerId,
                            BaseLinePriceDetail.WasteTypeId,
                            BaseLinePriceDetail.ContainerSizeId,
                            BaseLinePriceDetail.ProductCategoryId,
                            BaseLinePriceDetail.ProductTypeId,
                            BaseLinePriceDetail.ProductId,
                            BaseLinePriceDetail.ServiceId,
                            BaseLinePriceDetail.WasteSubTypeId,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
                            ELSE
                                CAST(CostHaulage AS NUMERIC(8,2))
                            END AS CostHaulage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
                            ELSE
                                CAST(CostTonnage AS NUMERIC(8,2))
                            END AS CostTonnage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
                            ELSE
                                CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
                            END AS SaleHaulage,
                            CASE
                                WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
                            THEN 
                                CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
                            ELSE
                                CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
                            END AS SaleTonnage,
                            IncludedTonnage
                        FROM
                            Site
                        INNER JOIN
                            [Postcodetools].[dbo].[PostcodeData] PCD ON REPLACE(PCD.PostCode,' ','') = REPLACE(Site.AddrPostcode,' ','')
                        INNER JOIN
                            BaseLinePriceSector ON BaseLinePriceSector.Sector = PCD.Sector
                        INNER JOIN
                            BaseLinePriceHeader ON BaseLinePriceHeader.BaseLinePriceHeaderId = BaseLinePriceSector.BaseLinePriceHeaderId 
                        INNER JOIN
                            BaseLinePriceDetail ON BaseLinePriceDetail.BaseLinePriceHeaderId = BaseLinePriceHeader.BaseLinePriceHeaderId
                        INNER JOIN  
                            Contractor ON Contractor.ContractorId = BaseLinePriceHeader.ContractorId
                        WHERE
                            (
                                (Site.SiteId = @SiteId) AND
                                (
                                    (
                                        (BaseLinePriceHeader.ContractorId = @ContractorId) AND
                                        (@ContractorId IS NOT NULL)
                                    ) OR
                                    (@ContractorId IS NULL)
                                ) AND
                                (Contractor.Active = 1) AND
                                (
                                    (
                                        (BaseLinePriceDetail.ContainerId = @ContainerId) AND
                                        (@ContainerId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.ContainerSizeId = @ContainerSizeId) AND
                                        (@ContainerSizeId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (BaseLinePriceDetail.ProductCategoryId = @ProductCategoryId) AND
                                        (@ProductCategoryId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.ProductTypeId = @ProductTypeId) AND
                                        (@ProductTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.ProductId = @ProductId) AND
                                        (@ProductId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (BaseLinePriceDetail.ServiceId = @ServiceId) AND
                                        (@ServiceId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
                                        (@WasteSubTypeId IS NOT NULL)
                                    ) 
                                ) OR
                                (
                                    (
                                        (BaseLinePriceDetail.ServiceId = @ServiceId) AND
                                        (@ServiceId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
                                        (@WasteTypeId IS NOT NULL)
                                    ) AND
                                    (
                                        (BaseLinePriceDetail.WasteSubTypeId IS NULL)
                                    ) 
                                )
                            )
                    ) a
                    WHERE
                        (
                            (a.BaselinePrice = a.Reseller)
                        )
            )

        /************************************************************************************************************************/
        /****** Select results  based on the MAx Level for that particular service (Waste or Product)                      ******/
        /************************************************************************************************************************/

        SELECT
            (
                SELECT
                    leveldescription,
                    contractorid,
                    contractorname,
                    contractorreference,
                    contractoraddress,
                    contractorfulladdress,
                    SUM(CostHaulage) AS costhaulage,
                    SUM(CostTonnage) AS costtonnage,
                    SUM(SaleHaulage) AS salehaulage,
                    SUM(SaleTonnage) AS saletonnage,
                    includedtonnage
                FROM
                    (
                        SELECT
                            CASE
                                WHEN
                                    (
                                        SELECT
                                            COUNT(RowNumber)
                                        FROM
                                            PriceList
                                        WHERE
                                            RowNumber = 1
                                    ) > 1
                                THEN 
                                    CASE Pricelist.Level 
                                        WHEN 50 Then 'Baseline'
                                    ELSE
                                        'Contractor Price'
                                    END
                                ELSE
                                    CASE Pricelist.Level 
                                        WHEN 10 Then 'Head'
                                        WHEN 20 Then 'Area'
                                        WHEN 30 Then 'Branch'
                                        WHEN 40 Then 'Site'
                                    ELSE
                                        'Baseline'
                                    END
                            END AS leveldescription,
                            Pricelist.ContractorId AS contractorid,
                            Pricelist.ContractorName AS contractorname,
                            Pricelist.ContractorReference AS contractorreference,
                            Pricelist.ContractorAddress AS contractoraddress,
                            Pricelist.ContractorFullAddress AS contractorfulladdress,
                            Pricelist.Reference AS reference,
                            Pricelist.CostHaulage AS costhaulage,
                            Pricelist.CostTonnage AS costtonnage,
                            Pricelist.SaleHaulage AS salehaulage,
                            Pricelist.SaleTonnage AS saletonnage,
                            Pricelist.IncludedTonnage AS includedtonnage
                        FROM
                            Pricelist
                        WHERE
                            RowNumber = 1
                    ) a
                    GROUP BY
                        leveldescription,
                        contractorid,
                        contractorname,
                        contractorreference,
                        contractoraddress,
                        contractorfulladdress,
                        includedtonnage
                    ORDER
                        BY SaleHaulage
                    FOR JSON PATH
            ) AS JSONData
)

Из запроса следует отметить одну интересную вещь: при запуске в SSMS текст запроса выглядит так

(@0 int,@1 int,@2 int,@3 varchar(8000),@4 varchar(8000))select * from [dbo] . [udf_JSON_QueryPricingForOrder_v5.0] ( @0 , null , null , @1 , null , null , null , null , @2 , @3 , @4 )

но при запуске из PHP для всех параметров используются огромные поля VARCHAR:

(@P1 nvarchar(4000),@P2 varchar(max),@P3 varchar(max),@P4 nvarchar(4000),@P5 varchar(max),@P6 varchar(max),@P7 varchar(max),@P8 varchar(max),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000))SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)