Click or drag to resize

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0110

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

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

 MessageLineColumn
1SA0110 : Avoid have stored procedure that contains IF statements.70
See Also

Other Resources