如何优化报表存储过程：移除IF ELSE改用WHERE子句提升效率

Got it, let's fix this by consolidating all logic into a single query with conditional filtering and dynamic sorting—no more branching required. Here's the refactored code:

ALTER PROCEDURE raport 
    @OD DATE, 
    @DO DATE, 
    @IMEI nvarchar(200) 
AS 
BEGIN
    -- Define how many records to fetch based on parameter state
    DECLARE @recordCount INT;
    SET @recordCount = CASE 
        WHEN ISNULL(@IMEI, '') <> '' AND ISNULL(@OD, '') <> '' AND ISNULL(@DO, '') <> '' THEN 1 
        ELSE 100 
    END;

    SELECT TOP (@recordCount) 
        Z.ID, 
        Z.JobNo, 
        Z.IMEI, 
        CAST(Z.DateBooked AS DATE) AS DataRejestracji, 
        Akcesoria = STUFF(
            (SELECT ',' + A.Accessory 
             FROM dbo.SPLIT(Z.Accessories, '/') new 
             INNER JOIN dbo.Accessories A ON new.items = A.Skrot COLLATE DATABASE_DEFAULT 
             FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'), 1, 1, ''
        ),
        JA.ID AS ID_JobsArch,
        JA.ActData,
        FLSymptomsCodes = STUFF(
            (SELECT ',' + FS.FLSymptomCode 
             FROM JobsSpares JS 
             INNER JOIN FLSymptomCodes FS ON JS.ID_FLSymptomCodes = FS.ID 
             WHERE js.id_jobs = Z.ID 
             FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'), 1, 1, ''
        ),
        @@servername AS [Nazwa Hosta],
        CASE WHEN Z.RepairDate IS NULL THEN 'NIE' ELSE 'TAK' END AS Naprawiony
    FROM ZTEPro.dbo.Jobs AS Z 
    INNER JOIN dbo.JobsArch JA ON Z.ID = JA.ID_Jobs
    -- Conditional filtering: only apply parameter checks if all three are non-empty
    WHERE 
        (
            ISNULL(@IMEI, '') <> '' 
            AND ISNULL(@OD, '') <> '' 
            AND ISNULL(@DO, '') <> '' 
            AND Z.IMEI = @IMEI 
            AND Z.DateBooked BETWEEN @OD AND @DO
        )
        OR
        (
            ISNULL(@IMEI, '') = '' 
            OR ISNULL(@OD, '') = '' 
            OR ISNULL(@DO, '') = ''
        )
    -- Switch sort order based on whether we're filtering or fetching latest records
    ORDER BY 
        CASE 
            WHEN ISNULL(@IMEI, '') <> '' AND ISNULL(@OD, '') <> '' AND ISNULL(@DO, '') <> '' THEN JA.Id_JobsArch 
            ELSE JA.ActData 
        END DESC;
END;

What Changed & Why: #

• Dynamic Record Count: We use a variable @recordCount set with a CASE statement to pick between TOP 1 (when all parameters are filled) and TOP 100 (when any parameter is empty). This replaces the IF ELSE branch for record limits.
• Unified WHERE Clause: The WHERE condition splits into two parts:
  • The first block applies your original filtering logic only when all three parameters have values.
  • The second block allows all records through when any parameter is empty, which lets us fetch the latest 100 as requested.
• Conditional Sorting: The ORDER BY uses a CASE statement to switch between sorting by Id_JobsArch (for filtered results) and ActData (for the latest 100 records), matching your original behavior.
• Single SELECT Block: All computed columns (like Akcesoria and FLSymptomsCodes) are kept in one place since they were identical in both original branches—no redundant code.

This keeps everything in a single query, removes the IF ELSE blocks, and maintains all your original functionality as requested.

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