sql-server-2005 - 在存储过程中执行 CTE 操作时出现无效对象错误？

Question

在存储过程中执行此操作时，出现无效对象 SubSkillIds 错误？谁能告诉我为什么会这样？

我的错误消息如下所示:-

Msg 208, Level 16, State 1, Procedure SubSkillDelete, Line 47
Invalid object name 'SubSkillIds'.

代码:

WITH SubSkillIds (SubSkillId) AS
(
   -- Base case
   SELECT
        SubSkillId
   FROM dbo.SubSkill
   WHERE RegressionSubSkillId = @SubSkillId
   UNION ALL
   -- Recursive step
   SELECT
        S.SubSkillId
   FROM dbo.SubSkill S
   WHERE S.RegressionSubSkillId IN (
   SELECT
        SubSkillId
   FROM dbo.SubSkill
   WHERE RegressionSubSkillId = @SubSkillId)
)

SELECT  @SubSkillIdFound = SubSkillId
FROM SubSkill WHERE SubSkillId = @SubSkillId

DELETE FROM SubSkillActivity WHERE SubSkillId = @SubSkillId
DELETE FROM SubSkill WHERE RegressionSubSkillId
IN (SELECT * FROM SubSkillIds)
DELETE FROM SubSkill WHERE SubSkillId = @SubSkillId

Answer 1

CTE 仅适用于下一条语句 - 您的 SELECT......

稍后它不再可用 - 这就是为什么引用该 CTE 的第二个 DELETE 语句将会失败。

如果您需要保留多个语句的 CTE 值，则需要将这些值存储到临时表/表变量中。