Getting an invalid Object error while doing CTE operation in Stored procedure?

Question

I got an error invalid Object SubSkillIds while executing this in a stored procedure? Can anybody let me know why is it so?

My error message looks like:-

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

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

The CTE is only available for the next statement - your SELECT......

It is no longer available later on - that's why your second DELETE statement, which references that CTE, will fail.

If you need to keep the values of the CTE around for more than one single statement, you need to store those values into a temporary table / table variable.