HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMajor

Transact SQL using WITH in CREATE VIEW

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createsqlwithviewusingtransact

Problem

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 :(

Solution

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

Code Snippets

WITH cte AS (...) SELECT ...;
CREATE VIEW
AS
WITH cte AS (...) SELECT ...;
GO
CREATE VIEW
AS
WITH
   cte1 AS (...),
   cte2 AS (...),
   cte3 AS (...)
SELECT ...
GO

Context

StackExchange Database Administrators Q#7239, answer score: 29

Revisions (0)

No revisions yet.