SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index

 1CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
 2@param1 int
 3AS
 4
 5SELECT   b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
 6FROM [Production].[BillOfMaterials] b
 7WHERE ( b.[ComponentID] + 5 > @param1)
 8
 9
10SELECT  b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
11FROM [Production].[BillOfMaterials] b
12WHERE ( b.[ComponentID] > (@param1 - 5))
13
14DECLARE  @StartProductID [int],
15    @CheckDate [datetime]
16
17BEGIN
18    SET NOCOUNT ON;
19
20    --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
21    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
22    AS (
23        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
24        FROM [Production].[BillOfMaterials] b
25            INNER JOIN [Production].[Product] p 
26            ON b.[ProductAssemblyID] = p.[ProductID] 
27        WHERE b.[ComponentID] = @StartProductID 
28            AND @CheckDate >= b.[StartDate] -1
29            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
30                        and p.[ProductID]  + 1 = 1234
31        UNION ALL
32        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
33        FROM [BOM_cte] cte
34            INNER JOIN [Production].[BillOfMaterials] b 
35            ON cte.[ProductAssemblyID] = b.[ComponentID]
36            INNER JOIN [Production].[Product] p 
37            ON b.[ProductAssemblyID] + 10 > 1200
38        WHERE @CheckDate >= b.[StartDate] + 1
39            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
40        )
41    -- Outer select from the CTE
42    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
43    FROM [BOM_cte] b
44
45    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
46    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
47    OPTION (MAXRECURSION 25) 
48
49SELECT * FROM [Production].[Product] p WHERE p.[ProductID]  /*IGNORE:SA0040*/ + 1 = 1234 AND p.[PerAssemblyQty] < 123
50
51END