SA0110 : Avoid have stored procedure that contains IF statements |
 |
The topic describes the SA0110 analysis rule.
MessageAvoid have stored procedure that contains IF statements
DescriptionThe 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.
How to fixConsider 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.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
Parameters| Name | Description | Default Value |
|---|---|---|
| IgnoreIfStatementsWhichDoNotContainDmlOrDddlStaetmetns |
Ignore IF statements which do not contain any DML or DDL statement. |
yes |
RemarksThe rule does not need Analysis Context or SQL Connection.
Effort To FixCategoriesDesign Rules, Bugs
Additional InformationThere is no additional info for this rule.
Example Test SQL|
SQL
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 + '%' |
Analysis Results| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0110 : Avoid have stored procedure that contains IF statements. | 7 | 0 |
See Also