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.
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.
Design Rules, New Rules
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
|1||SA0243 : Avoid INSERT-EXECUTE in stored procedures.||8||13|
© Ubitsoft Ltd. All Rights Reserved.