Getting an invalid object error while performing a CTE operation in a stored procedure?

Question

Am I getting an error with the Object SubSkillIds error when doing this in a stored procedure? Can anyone tell me why this is?

My error message looks like this: -

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

the code:

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 is available only for the next operator - your SELECT......

Now it is no longer available - why your second statement DELETE, which refers to the CTE, does not work.

CTE , table/table.