T-sql – Transact SQL using WITH in CREATE VIEW

t-sqlview

I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.

I want smth like this

WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl

And what is the correct syntax for using several WITH clauses?

Nothing useful on MSDN 🙁

Best Answer

The CTE goes inside the view.

Take a query with a CTE

WITH cte AS (...) SELECT ...;

Just add CREATE VIEW AS .. GO

CREATE VIEW
AS
WITH cte AS (...) SELECT ...;
GO

MSDN does describe multiple CTEs (See example j)

CREATE VIEW
AS
WITH
   cte1 AS (...),
   cte2 AS (...),
   cte3 AS (...)
SELECT ...
GO
Related Question