Scope of an CTE in SQL Server 2005

Viewed 10416
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

2 Answers
Related