SQL Server 2014使用游标分配销售订单库存遇ctee对象无效报错

核心原因：CTE作用域问题 #

SQL Server中CTE（公用表表达式）的作用域仅局限于定义它的WITH子句之后紧跟的第一条T-SQL语句。
你现在的代码顺序存在问题：

WITH CTW(...), CTEB(...), CTEC(...), ctee(...)
AS (...)
-- 第一条语句：插入变量值，执行完后CTE就会被销毁
INSERT INTO @allocation VALUES (@L_U,@C_U,@L_PART,@L_PHY ,@R_T,@ORD_RUN ,@T_L ,@FINAL_T_L)
-- 第二条语句：此时ctee已经失效，自然报无效对象错误
select * from ctee

同时你还存在语法混写问题：把INSERT ... VALUES和INSERT ... SELECT无分隔拼在一起，本身也是语法错误。

其他关联错误 #

1. 开头的IF OBJECT_ID('[dbo].[@allocation]') is not null DROP TABLE语句无效：@allocation是表变量，不是永久表/临时表，不会在系统表中生成对应的OBJECT_ID，这段逻辑可以直接删除。
2. 你要插入的@L_U、@C_U等变量没有做任何赋值操作，就算CTE不报错，插入的也都是空值，完全达不到同步分配记录的目标。
3. 同一循环内重复两次插入@allocation表，会导致重复数据插入。
4. 库存编码写死为105165，循环所有订单行都只会分配该编码的库存，不符合业务逻辑。

你可以直接把CTE的查询结果插入到分配表，不需要额外用变量中转，参考写法：

declare @RESULT AS TABLE(COR_UNIQUE VARCHAR(20),COR_PART_ONLY varchar(16),COR_OUR_NUMBER  varchar(16),COR_QTY_ORDERED   decimal(18,5) );
declare @allocation AS TABLE(L_U VARCHAR(20),C_U VARCHAR(20),L_PART varchar(16),L_PHY DECIMAL(18,5),R_T DECIMAL(18,5),ORD_RUN DECIMAL(18,5),T_L DECIMAL(18,5),FINAL_T_L DECIMAL(18,5));
DECLARE @COR_UNIQUE AS VARCHAR(20),@COR_PART_ONLY AS varchar(16),@COR_OUR_NUMBER  AS varchar(16),@COR_QTY_ORDERED as decimal(18,5);

DECLARE cursor_results CURSOR FOR
with ctea(COR_UNIQUE,COR_PART_ONLY,COR_OUR_NUMBER,COR_QTY_ORDERED) as (
 SELECT  [COR_UNIQUE]
      ,[COR_PART_ONLY]
      ,[COR_OUR_NUMBER]
      ,[COR_QTY_ORDERED]
  FROM [RMC_ASC_TEST].[dbo].[ASC_COR_TBL]
  where COR_OUR_NUMBER_N ='8215258'
  and [COR_UNIQUE] = '437145'
)
select COR_UNIQUE,COR_PART_ONLY,COR_OUR_NUMBER,COR_QTY_ORDERED from ctea
 
OPEN cursor_results;
FETCH NEXT FROM cursor_results into @COR_UNIQUE,@COR_PART_ONLY,@COR_OUR_NUMBER,@COR_QTY_ORDERED;
WHILE @@FETCH_STATUS = 0
BEGIN 
	WITH CTW(L_UN,F_T_L) AS (
		SELECT L_U AS L_UN, SUM(FINAL_T_L) AS F_T_L
		FROM @allocation
		GROUP BY L_U
	),
	CTEB(LOT_UNIQUE_B,LOT_PART_ONLY_B,LOT_PHYSICAL_B,RUNNING_TOTAL_B) AS (
		SELECT 
			LOT_UNIQUE,
			LOT_PART_ONLY,
			LOT_PHYSICAL- ISNULL(F_T_L,0) AS LOT_PHYSICAL,
			SUM(LOT_PHYSICAL - ISNULL(F_T_L,0)) over (order by LOT_EXPIRY_DATE) AS RUNNING_TOTAL
		FROM [dbo].[ASC_LOT_TBL]
		LEFT JOIN CTW ON L_UN = LOT_UNIQUE
		where LOT_PART_ONLY = @COR_PART_ONLY -- 用游标变量替换硬编码
	),
	CTEC(L_U,C_U,L_PART,L_PHY,R_T,ORD_RUN,T_L,FINAL_T_L) AS (
		SELECT 
			LOT_UNIQUE_B AS L_U,
			@COR_UNIQUE as C_U,
			LOT_PART_ONLY_B AS L_PART,
			LOT_PHYSICAL_B AS L_PHY,
			RUNNING_TOTAL_B AS R_T, 
			RUNNING_TOTAL_B-@COR_QTY_ORDERED AS ORD_RUN,
			case
				when RUNNING_TOTAL_B-@COR_QTY_ORDERED <=0 then LOT_PHYSICAL_B
				WHEN RUNNING_TOTAL_B - LOT_PHYSICAL_B < @COR_QTY_ORDERED THEN @COR_QTY_ORDERED - (RUNNING_TOTAL_B - LOT_PHYSICAL_B)
				ELSE 0
			end as T_L,
			CASE WHEN T_L <= 0 THEN 0 ELSE T_L END AS FINAL_T_L
		FROM CTEB
	)
	-- 直接在CTE作用域内执行插入，不会报错
	INSERT INTO @allocation(L_U,C_U,L_PART,L_PHY,R_T,ORD_RUN,T_L,FINAL_T_L)
	SELECT L_U,C_U,L_PART,L_PHY,R_T,ORD_RUN,T_L,FINAL_T_L 
	FROM CTEC 
	WHERE FINAL_T_L > 0 -- 过滤无分配量的记录

	FETCH NEXT FROM cursor_results into @COR_UNIQUE,@COR_PART_ONLY,@COR_OUR_NUMBER,@COR_QTY_ORDERED;
END
CLOSE cursor_results;
DEALLOCATE cursor_results;

-- 查看最终分配结果
SELECT * FROM @allocation

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