Scope of an CTE in SQL Server 2005

Question

WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM emp_CTE

This works fine

If the same query is written like this.

WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM EMPLOYEES    
SELECT * FROM emp_CTE

it gives a message telling emp_CTE does not exist.

Is there any way we can overcome this issue?

thanks Prince

Answer 1

The CTE is part of the subsequent statement only.

The subsequent statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION, INTERSECT etc)

For example:

;WITH cte1 AS
(
   select ...
), cte2 AS
(
    select ...
)
SELECT ...
UNION 
SELECT ...;

The rule of thumb is that the scope is until where next ; would be. A semi-colon terminates any statement but is optional unfortunately.

Your failing code above is actually this

...;
WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM EMPLOYEES; 
SELECT * FROM emp_CTE;

So the CTE is only in scope up until ...EMPLOYEES;