Common Table Expression (CTE) with a View in SQL Server 2005/8Posted: May 25, 2011
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: