Scope

When running EXECUTE() or sp_execute(), the SQL is executed within it’s own scope and does not inherit the scope of the calling stored procedure. This results in the following behavior:

• Permissions are not inherited so the calling user must have direct permissions for all the objects involved. There are some options to address this in SQL 2005 (for example, certificates and/or impersonation) but not in SQL 2000.
• No direct access to local variables or parameters of the calling stored procedure without passing them.
• Any USE statement in the dynamic SQL will not affect the calling stored procedure.
• Temp tables created in the dynamic SQL are not accessible from the calling procedure as they are dropped when the dynamic SQL exits. The block of dynamic SQL can however access temp tables created by the calling procedure.
• When you issue a SET command in the dynamic SQL, then the effect of the SET command lasts for the duration of the block of dynamic SQL only and does not affect the caller.
• The query plan for the stored procedure does not include the dynamic SQL. The block of dynamic SQL has a query plan of its own.