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, Bugs
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|