[pohart]

CTEs aren't for creating a simple value. They are for creating a table variable.

[ZenoArrow]

I know what CTEs are for (in practical terms they're useful for subqueries you need to reference more than once), and like I said before, you can define temporary tables with the DECLARE keyword. To give you an example, this is valid T-SQL code...

DECLARE @temptable TABLE (id INT, fulldescription NVARCHAR(MAX));