Menu
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)
)
616
asked Jul 20 '11 17:07
The main difference between the EXEC or EXECUTE operators and the sp_executesql built-in stored procedure is that the EXEC operator is used to execute a stored procedure or a SQL command passed as a string or stored within a variable.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
sp_executesql allows for statements to be parameterized, Therefore It's more secure than EXEC in terms of SQL injection.
The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.
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.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
