在SQL中用多个CTE填充表的存储过程中出错

Question

我创建并修改了我的存储过程，但是当执行它时，我有一个错误:";(0行受影响)Msg 208，级别16，状态1，过程Sp_Inventory_PrMonth，第112行[批处理开始行139]无效的对象名称'report_cte'."我的问题是没有用";"在此之前，我无法解决这个问题。

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

我期望我的过程获得日期参数并在表中填充前一个月的日期数据。

Answer 1

我找到了解决方案，在CTE之后我们不能使用Delete语句，所以我不得不使用"select into "首先从临时表中导入数据然后删除语句然后从临时表中插入到我的表