Using EXEC () or SP_EXECUTESQL with SQL Common Table Expressions

Question

How to use EXEC (@SQL) or EXEC SP_EXECUTESQL (@SQL) using common table expressions?

Below does not work.

WITH CTE_Customer (ID,  Name)
AS
(
    EXEC (@strSqlCommand)
)

Answer 1

Short answer: you cannot:

http://msdn.microsoft.com/en-us/library/ms175972.aspx says: "The definition of CTE_query_definition must meet the same requirements as for creating the view." This basically means that you are limited only to SELECT statements.

, .