But you can access a local variable.

DECLARE @Start int

SET @Start = 3

;WITH NumberSequence ( MonthNumber )

AS (

-- Anchor Member

SELECT @Start AS MonthNumber

UNION ALL

-- Recursive Member

SELECT MonthNumber + 1

FROM NumberSequence

WHERE MonthNumber < 12

)

-- Outer Query

SELECT *

FROM NumberSequence

I just didn't know you couldn't assign to a variable from a CTE.

I understand that a CTE only exists for the duration of the query and that would make sense if the variable were inside the CTE (I know it isn't possible) but whether the CTE is temporary doesn't really explain why you can't write to a variable that is outside the CTE, but you can access that same variable in the CTE.

Thanks,

Tom