What are the real-world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using
EXEC SP_EXECUTESQL @SQL
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:
You can read this "The Curse and Blessings of Dynamic SQL"