Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

Question

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

?

Answer 1

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"