SQL Server多CTE存储过程报错：无效对象名'report_cte'

根本问题 #

CTE（公用表表达式）的作用域仅限定义完成后的第一条SQL语句。你的代码中，在report_cte定义结束后先执行了DELETE操作，此时CTE的生命周期已结束，后续INSERT语句再调用report_cte就会提示找不到对象。

修复方案 #

将DELETE语句移至CTE定义之前执行，确保CTE定义后紧跟需要调用它的INSERT语句：

ALTER PROCEDURE Sp_Inventory_PrMonth
    (@currentdate DATE)
AS
BEGIN
    DECLARE  @first_day_prior_month DATE, @last_day_prior_month DATE;

    -- Calculate the first and last day of the prior month
    SET @first_day_prior_month = DATEADD(mm, DATEDIFF(mm, 0, @currentdate) - 1, 0); 

    SET @last_day_prior_month = EOMONTH(@first_day_prior_month);

    -- 先执行删除操作，不影响后续CTE的作用域
    DELETE FROM [dbo].[Floorplan_Summary]
    WHERE [Period] = CONVERT(varchar(6), @first_day_prior_month, 112);

    -- 定义CTE，之后直接执行INSERT调用它
    ;WITH [dates_cte] AS 
    (
        SELECT * 
        FROM (VALUES ('2019-09-01', '2019-09-30', '201909'),
                     ('2022-10-01', '2022-11-01', '202210'),
                     ('2022-11-01', '2022-12-01', '202211')) AS [t]([start_date], [end_date], [period])
    ),
    [inventory_cte] AS
    (
        SELECT
            [vi].[database_id],
            [YYYYMM] = [d].[period],
            [State] = CASE 
                          WHEN [vi].[database_id] LIKE 'STORE4%' 
                              THEN 'QLD' 
                              ELSE 'NSW' 
                      END,
            [vi].[nkey],
            [Type] = CASE 
                         WHEN [vi].[VehicleInventoryTypeID] = 'Used' 
                             THEN 'Used'
                             ELSE CASE 
                                      WHEN ([vi].[StatusID] IN ('4', '7', '8') OR ([vi].[StatusID] IN ('6', '13') AND [vi].[PreSaleStatusID] IN ('4', '7', '8'))) 
                                          THEN 'Demo'
                                          ELSE 'New' 
                                  END
                     END,
           [Location_Code] = [vi].[database_id] + '_' + [vi].[LocationID],
           [Make] = CASE 
                        WHEN [vi].[VehicleInventoryTypeID] = 'Used' 
                            THEN 'Used' 
                            ELSE [vi].[ManufacturerID] 
                    END,
           [vi].[StatusID],
           [vi].[ReceiptDate],
           [vi].[DeliveryDate],
           [vi].[ActivityDate],
           [vi].[CostAmount],
           [Vi].[VehicleInventoryTypeID]
       FROM
           [PDW_SQLSERVER].[510102_DataWarehouse].[dbo].[VehicleInventory] AS [vi]
       INNER JOIN
           [dates_cte] AS [d] ON [vi].[ReceiptDate] < [d].[end_date]
                              AND (([vi].[StatusID] NOT IN ('6', '13'))
                                   OR ([vi].[StatusID] IN ('6') AND [vi].[DeliveryDate] > [d].[end_date])
                                   OR ([vi].[StatusID] IN ('13') AND [vi].[ActivityDate] > [d].[end_date]))
                              AND [vi].[StatusID] NOT IN ('9')
                              AND [vi].[LocationID] NOT IN ('ORD', 'NHY', 'CHTA', 'SMA')
       WHERE 
           [vi].[database_id] IN ('STORE201', 'STORE214', 'STORE217', 'STORE401')
    ),
    [report_cte] AS
    (
        SELECT 
            [i].[YYYYMM],
            [i].[State],
            [i].[Location_Code],
            [i].[Make],
            [i].[Type],
            [i].[nkey],
            [i].[CostAmount]
        FROM 
            [inventory_cte] AS [i]
    )
    -- Insert the data for the prior month into the table
    INSERT INTO [dbo].[Floorplan_Summary]
                     ([Period], [State], [Location_Code], [Make],[Type],
                      [Floorplan_Unit_Count], [Floorplan_Cost_Amount])
        SELECT
            [t].[YYYYMM], [t].[State], [t].[Location_Code],
            [t].[Make], [t].[Type],
            [n] = COUNT([t].[nkey]), 
            [Total] = SUM([t].[CostAmount])
        FROM
            [report_cte] AS [t]
        WHERE
            [t].[YYYYMM] = CONVERT(varchar(6), @first_day_prior_month, 112)
        GROUP BY
            [t].[YYYYMM], [t].[State], [t].[Type],
            [t].[Location_Code], [t].[Make]
    END

    --execute Sp_Inventory_PrMonth '2022-12-10'

补充说明 #

如果后续需要CTE同时支撑多个操作（比如DELETE和INSERT都依赖CTE数据），可以先将CTE结果存入临时表，再基于临时表执行后续操作，彻底规避作用域限制。

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