Seema;

You will find that CTE's work quite well within dynamic sql.

Here is an example that can be used from any database in SQL Server 2005. It is not actually useful at all, but it demonstrates the concept:

declare @Sql varchar(8000)

set @Sql = '

with CTE1 as

(

select * from information_schema.columns

)

select * from CTE1

'

exec (@sql)

Now, what you may be thinking of is the fact you cannot define the CTE from outside of the dynamic SQL variable and then use it within the dynamic SQL call, for instance:

--Note this does not work

declare @Sql varchar(8000);

with CTE1 as

(

select * from information_schema.columns

)

set @Sql = '

select * from CTE1

'

exec (@sql)

Gives the error that there is incorrect syntax near set. Keep in mind that a CTE is considered part of its accompanying SELECT, INSERT, UPDATE, or DELETE statement and cannot exist on its own.

You can consider either putting the CTE inside of the dynamic sql variable along with its main statement or else look at creating a view. Keep in mind that if for some reason you do not want the view to be persistent you can simply drop it immediately after it is used.

Of course, at the risk of getting off topic, always be careful to ensure you need dynamic SQL. As Erland Sommarskog has point out, dynamic SQl is both a great blessing and a great curse. http://www.sommarskog.se/dynamic_sql.html

Edited at 1600 to fix a typo.