SA0110 : Avoid have stored procedure that contains IF statements
The topic describes the SA0110 analysis rule.
Avoid have stored procedure that contains IF statements
The rule checks and alerts for usage of IF/IF..ELSE statements in stored procedure’s body.
Avoid having conditional logic in a stored procedures, functions and triggers because it could confuse the SQL query optimizer and cause problems with parameter sniffing.
Consider moving the conditionally executed code in separate stored procedures that are executed based on the results of your conditional logic in a “main” stored procedure.
The rule has a Batch scope and is applied only on the SQL script.
Ignore IF statements which do not contain any DML or DDL statement.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Bugs
There is no additional info for this rule.
1CREATE PROCEDURE testsp_SA0110 2( 3 @Code VARCHAR(30) = NULL 4) 5AS 6 7IF @Code IS NULL 8 SELECT * FROM Table1 9ELSE 10 SELECT * FROM Table1 WHERE Code like @Code + '%'
|1||SA0110 : Avoid have stored procedure that contains IF statements.||7||0|