Click or drag to resize

SA0175 : Extract input expression as a variable in order to ensure it is invariant and avoid unexpected results

The topic describes the SA0175 analysis rule.

Message

Extract input expression as a variable in order to ensure it is invariant and avoid unexpected results

Description

The rule checks simple CASE expressions and COALESCE function for having an input expression, which is not invariant.

Internally SQL Server converts the simple CASE expressions are evaluated as searched CASE expressions.

In cases when the input expression isn't deterministic, for example RAND() function, it can give unexpected results.

It is better to evaluate the input expression once and assign it to a variable , then to use this variable as CASE's input expression.

This rule reports for the following functions is used in a CASE input expression:

RAND, NEWID,NEWSEQUENTIALID , CRYPT_GEN_RANDOM

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
 1DECLARE  @c INT = 5
 2
 3SELECT  CASE CONVERT( SMALLINT, RAND() *@c)
 4            WHEN 1 THEN 'a'
 5            WHEN 2 THEN 'b'
 6        END
 7
 8DECLARE  @test SMALLINT = CONVERT( SMALLINT, RAND() *@c)
 9
10SELECT  CASE @test
11            WHEN 1 THEN 'a'
12            WHEN 2 THEN 'b'
13        END

Analysis Results

 MessageLineColumn
1SA0175 : Extract COALESCE input expresison as a varibale in order to avoid unexpected results. The RAND function will be evaluated for each of the conditions and this may lead to unexpected results.332
See Also

Other Resources