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.
Extract input expression as a variable in order to ensure it is invariant and avoid unexpected results
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
Extract the input expression as a variable in order to ensure it is invariant and avoid unexpected results, and then use the variable in CASE expression or COALESCE function.
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Design Rules, Bugs
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
|1||SA0175 : 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.||3||32|