Click or drag to resize

SA0243 : Avoid INSERT-EXECUTE in stored procedures

The topic describes the SA0243 analysis rule.

Message

Avoid INSERT-EXECUTE in stored procedures

Description

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.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
SQL
 1CREATE PROCEDURE TestProc 
 2@param1 varchar(40),
 3@param2     smallint AS
 4DECLARE @data AS TABLE 
 5     (col1 varchar(100) NOT NULL PRIMARY KEY,
 6      col2   smallint    NOT NULL)
 7
 8INSERT @data EXEC GetTestData @param1
 9
10SELECT * FROM @data

Analysis Results

 MessageLineColumn
1SA0243 : Avoid INSERT-EXECUTE in stored procedures.813
See Also

Other Resources