>Is there a way to have a Common Table Expression valid for more than one command in the script? Presently, I have to repeat the same command again, later down the script, so it would be available for other commands.


As Naomi and Borislav said, you can't. The options are to redirect to a temp table, table variable, etc. if you need to use it multiple times. In that instance, it's questionable if a CTE is really necessary - you might not be gaining anything (unless you were using the CTE for recursion).

In case you're curious, the reason you can't is this....a CTE is really just syntax sugar - it allows you to take the syntax for a derived table subquery and place it "above" the outer query that uses it. If you actually try to run the CTE code itself without executing the subsequent line that uses it, you'll get an error.

So the database engine is really just substituting the reference to the CTE in the main query with the SELECT statement in the CTE. Sort of like expanding a macro. So it's all gone after the line of code that uses the CTE.