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

Advertisements

One Comment on “Common Table Expression (CTE) with a View in SQL Server 2005/8”

  1. James says:

    I was able to create a CTE inside a view. See the link below.
    http://www.sqlusa.com/bestpractices2005/viewwithcte/

    Am i missing something?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s