SA0243 : Avoid INSERT-EXECUTE in stored procedures

The topic describes the SA0243 analysis rule.


Avoid INSERT-EXECUTE in stored procedures


The rule checks stored procedures for uses of the INSERT with EXECUTE statement.

INSERT-EXECUTE provides a simple method for the result of a stored procedure to be inserted directly in a table without need of any changes to the procedure.

The method has some issues, which have to be considered:

– An INSERT-EXECUTE statement cannot be nested. For example if a stored procedure or any stored procedure called by this procedure uses an INSERT-EXECUTE statement, the stored procedure cannot be used in an INSERT-EXECUTE statement.

– The result set form the called procedure has to exactly match the column list in the INSERT statement or when a column list is not specified – the columns in the target table. If the procedure result set is changed for some reason, the INSERT-EXECUTE will fail.

– Even if there is no explicit transaction started, the stored procedure is executed in the context of the INSERT statement’s transaction.

– Error handling can be trickier as it is not permitted to use ROLLBACK TRANSACTION inside the called procedure.

– The nesting restriction remains with executing dynamic SQL using the sp_executesql and have to be considered.

For more details about the issues, check the additional information section links.

How to fix

Consider the issues that the INSERT with EXECUTE statement has, and use a suppression comment to suppress the rule violation.


The rule has a Batch scope and is applied only on the SQL script.


Rule has no parameters.


The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 hours per issue.

Design Rules, Bugs

Additional Information
Example Test SQL
 2@param1 varchar(40),
 3@param2     smallint AS
 5     (col1 varchar(100) NOT NULL PRIMARY KEY,
 6      col2   smallint    NOT NULL)
 8INSERT @data EXEC GetTestData @param1
10SELECT * FROM @data

Analysis Results
  Message Line Column
1 SA0243 : Avoid INSERT-EXECUTE in stored procedures. 8 13
See Also

Other Resources