Error in stored procedure for populating a table with multiple CTE in SQL Server

Question

I created and alter my stored procedure, but when execute it I get an error:

Msg 208, Level 16, State 1, Procedure Sp_Inventory_PrMonth, Line 112 [Batch Start Line 139]
Invalid object name 'report_cte'

My issue is not using ";" before with, I couldn't solve it.

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);

    ;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]
    )
    -- Delete any existing data from the prior month from the table
    DELETE FROM [dbo].[Floorplan_Summary]
    WHERE [Period] = CONVERT(varchar(6), @first_day_prior_month, 112) 

    -- 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'

I expected my procedure get the date parameter and populate data of prior month of date in the table.

Answer 1

I found the solution, after CTE we couldn't use Delete statement, so I have to use "select into " and import data at first in a temp table then delete statement and then insert to my table from temp table