Dynamic SQL in a CTE?

Hi all,

Is it possible to execute dynamic SQL in a CTE? that is create the dynamic SQL, stick it into a variable 'strCriteria' and then execute 'strCriteria' within the CTE?

WITH UPCTE

AS

(

this doesn't work
Exec sp_sqlexec @.strCriteria

)

SELECT DISTINCT Comp_Name FROM UPCTE;

Meltdown:

Will something like this get you through as a workaround?

declare @.baseCTEDefinition varchar (500)
declare @.theSelectStatement varchar (500)

set @.baseCTEDefinition = 'with myCTE as ( select 1 as singlet ) '
set @.theSelectStatement = 'select * from myCTE '

exec ( baseCTEDefinition + @.theSelectStatement )

Dave

|||Can you please explain why you need to use dynamic SQL within CTE? CTE is similar to a view, it is a declarative construct, the definition of the CTE is expanded in all references in the query, compiled, optimized and executed. So you cannot execute dynamic SQL or call SPs directly from CTE. If you just want to execute a SQL statement dynamically then use EXEC or sp_executesql. Also, sp_sqlexec has been deprecated for a while and it will be removed soon from the product. So you should remove those from your code also. Lastly, why do you need to use dynamic SQL and what are you trying to solve that requires use of dynamic SQL. If you can explain your problem it will be helpful to suggest easier solutions.