The topic describes the DF102 T-SQL code analysis rule.
EXECUTION RULES
INSERT-EXECUTE is used in stored procedure.
It’s recommended to avoid using INSERT-EXECUTE in stored procedures, as it can lead to unexpected behavior and potential issues with data integrity and performance.
INSERT-EXECUTE statements cannot be nested. If a stored procedure or any procedure called by it uses INSERT-EXEC, it cannot be invoked within another INSERT-EXECUTE statement. This limitation complicates code organization and can restrict the flexibility of your procedures.
The result set from the called procedure must exactly match the column list specified in the INSERT statement or the columns in the target table. Any deviation in the result set structure can cause the INSERT-EXECUTE statement to fail, leading to unexpected behavior.
Additionally, error handling becomes trickier with INSERT-EXECUTE statements. The use of ROLLBACK TRANSACTION inside the called procedure is not permitted, complicating error recovery and making it harder to handle exceptional conditions gracefully.
Even if no explicit transaction is started, the called procedure is executed within the context of the INSERT statement’s transaction. This can affect transaction management and isolation levels, potentially leading to unintended side effects.