0 votes
1 view
in SQL by (20.3k points)

What are the real-world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using

EXEC (@SQL)

versus

EXEC SP_EXECUTESQL @SQL

?

1 Answer

0 votes
by (36.7k points)

sp_executesql is used to promote query plan reuse. Parameters will be explicitly identified in the calling signature if you are using sp_executesql, . 

Have a look at this article to learn about this process:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966425(v=technet.10)

or 

You can read this "The Curse and Blessings of Dynamic SQL"

...