Just a reminder of what a great tool this stored proc is for keeping dynamic SQL in line. As Books Online points out in Using sp_executesql, the parameter substitution support usually makes it both more flexible and more efficient than EXECUTE.
In addition to the benefits outlined in BOL (primarily enhanced execution plan re-use) it can be useful for things like:
- Error handling: Some errors cause the current plan to be aborted before error-handling code is reached. This can be mitigated by using sp_executesql to execute the call. You can check @@ERROR for the error code, or check the return value of sp_executesql. Won't work if the whole batch is aborted, but will help where an individual plan fails.
- Retrieving output data: You can use an output parameter with sp_executesql to retrieve an output variable from a dynamic SQL query. As so often is the case, there's a KB article on how to do it: INF: Using Output Parameters with sp_executesql.
I'm sure there are other scenarios where it helps - anyone have anything to add?