SA0110 : Avoid have stored procedure that contains IF statements

The topic describes the SA0110 analysis rule.

Message

Avoid have stored procedure that contains IF statements

Description

The rule checks and alerts for usage of IF/IF..ELSE statements in stored procedure’s body.

Avod having conditional logic in a stored procedures, functions and triggers because it could confuse the SQL query optimizer and cause problems with parameter sniffing.

Scope

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

Parameters
Name Description Default Value
IgnoreIfStatementsWhichDoNotContainDmlOrDddlStaetmetns

Ignore IF statemetns which do not contain any DML or DDL statemetnt.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test Script
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

Other Resources