Common Table Expression (CTE) with a View in SQL Server 2005/8

You cannot use Common Table Expression directly inside a View with SQL Server 2005/8  but you can use an inline table function to wrap the CTE returned as table. You can call the function inside the view as you would with a table.

Here is the sample code with my early recusive CTE sample;

Step 1 to create the function:

 

CREATE FUNCTION [dbo].[mytableFunction1]

()

RETURNS TABLE

AS

RETURN

(

with mycte as

(

SELECT 1 as i, CHar(ascii('A')) as myletter

UNION ALL

select i+1 , Char(ascii('A')+i) FROM mycte WHERE i<26

)

SELECT * FROM mycte

)

Step 2 to create the view:

CREATE VIEW [dbo].[View_CTE]

AS

SELECT i, myletter    FROM dbo.mytableFunction1() AS mytableFunction1_1

Edit: Thanks to James for pointing the error from the posting.
Here is the link from MSDN:
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx

---