You might try bol (bools online) for the topics "sp_executesql" and "Using sp_executesql". The object (e.g., table) names need to be fully qualified in order for SQL Server to reuse a cached execution plan.

Also, the execution of the query is very much like (maybe it's the same except for permissions) as calling a nested stored procedure. It inherits connection properties, but setting connection properties in the query are temporary. The query also has access to temporary tables and cursors declared in the calling batch. If the calling batch is a stored procedure, permission to the procedure does not translate to permission to execute the ad hoc query. The user would need permission to execute the proc plus permission to do what’s in the query executed by the proc. (Imagine having an ad hoc query string passed to the procedure that drops databases.)

The batch executing the query (also a batch) can trap or handle syntax errors if they are returned by the execution of the query. If you ignore them, the original batch will continue executing any statements following.