sqlsql-serverwith-statementwith-clause

Several usage of sql with alias

I want to use several time alias of with clause on SQL server.

Example:

WITH name1 AS (
     SELECT ...
)

SELECT * from name1
SELECT * from name1 ORDER BY name

Is it possible? I'm getting "Invalid object name " error

Solution

  • What you are trying to use is a CTE, which is available for use only in the immediately following DML

    WITH name1 AS (
     SELECT ...
)

SELECT * from name1

    That part will work fine. The next select statement will not have access to the CTE. You can try using a table variable instead