5

How do I use EXEC(@SQL) or EXEC SP_EXECUTESQL(@SQL) with Common Table Expressions?

Below does not work.

WITH CTE_Customer (ID,  Name)
AS
(
    EXEC (@strSqlCommand)
)
Deepfreezed
  • 567
  • 2
  • 10
  • 18

1 Answers1

6

The short answer is that you cant:

http://msdn.microsoft.com/en-us/library/ms175972.aspx says: "The CTE_query_definition must meet the same requirements as for creating a view" Which basically says that you're restricted to SELECT statements only.

Some workarounds might include using temp tables or table variables, but it really depends on context.

Michael J Swart
  • 3,060
  • 3
  • 29
  • 46