Is there a way to write any kind of code before selecting your CTE table ?
DECLARE @TestTable TABLE (ID INT ,name NVARCHAR)
INSERT INTO @TestTable VALUES (1,'a'),(2,'b'),(1,'c')
;WITH TempCte(name)
AS
(
SELECT name FROM @TestTable WHERE ID = 1
)
PRINT 'test'
SELECT * FROM TempCte
No, that cannot be done. If you refer MSDN then :
A common table expression (CTE) can be thought of as a temporary result set
that is defined within the execution scope of a SINGLE
SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
So basically it's the scope of a SINGLE SELECT/ INSERT/ UPDATE/ DELETE/ or CREATE VIEW statement which holds the CTE result set. Anything written after that scope won't be able to access this Temporary data.You can read more here: http://msdn.microsoft.com/en-us/library/ms175972.aspx
