Unable to use a temporary table more than once
Community wiki answer generated from comments to the question by various people.

Despite the name, you are declaring #results as a Common Table Expression (CTE), not a temporary table. CTEs only apply to the same statement they are defined in.

You can solve your problem using a temporary table, using syntax like:

CREATE TABLE #mytable (&lt;columns&gt;); 
INSERT INTO #mytable...


or

SELECT * 
INTO #results
FROM ...




Additional comments by the author:

As Pointed out by Shanky, I am using a CTE instead of a temp table, which as Queue Man said, loses scope when I assign it to a variable.

So I ended up creating a temp table and inserting the contents of my CTE #results as suggested by JNK.