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

Using non-deterministic functions such as RAND(), NEWID(), or CRYPT_GEN_RANDOM() in simple CASE or COALESCE expressions can cause inconsistent results due to multiple evaluations of these expressions in SQL Server.

Description

This problem arises when non-deterministic functions are used as expressions in simple CASE or COALESCE. In SQL Server, these expressions are evaluated multiple times, potentially leading to inconsistent results. This issue is often seen with functions like RAND(), NEWID(), and CRYPT_GEN_RANDOM(), which can return different values each time they are evaluated.

For example:

SQL
Copy
1-- Example of problematic usage in a simple CASE expression
2SELECT 
3    CASE RAND() 
4        WHEN 0.5 THEN 'Half'
5        ELSE 'Other'
6    END AS ValueCategory;

In this example, the same call to RAND() can yield different evaluations within the same query, leading to unexpected outcomes and logical errors. This behavior is contrary to the intended stability of a CASE expression’s input.

  • Variation in results: Non-deterministic functions might yield different values on repeated calls.

  • Unintended behavior in logic: Decision-making constructs may produce inconsistent outputs.

How to fix

Ensure expressions in simple CASE statements and COALESCE function calls are deterministic to avoid unexpected results.

Follow these steps to address the issue:

  1. Identify the non-deterministic expressions used within simple CASE statements or COALESCE function calls. Common non-deterministic functions include RAND(), NEWID(), and CRYPT_GEN_RANDOM().

  2. Extract the non-deterministic expression as a variable using a SELECT statement to ensure it is evaluated once and remains invariant.

  3. Replace the non-deterministic expression in the CASE or COALESCE construct with the variable created in the previous step.

For example:

SQL
Copy
1-- Corrected usage with variable assignment
2DECLARE @RandomValue FLOAT = RAND();
3
4SELECT 
5    CASE @RandomValue
6        WHEN 0.5 THEN 'Half'
7        ELSE 'Other'
8    END AS ValueCategory;

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.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 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
  Message Line Column
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
See Also

Other Resources

Analysis Rules