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

Introduction

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:

-- Example of problematic usage in a simple CASE expression
SELECT
    CASE RAND()
        WHEN 0.5 THEN 'Half'
        ELSE 'Other'
    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:

-- Corrected usage with variable assignment
DECLARE @RandomValue FLOAT = RAND();

SELECT
    CASE @RandomValue
        WHEN 0.5 THEN 'Half'
        ELSE 'Other'
    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.

Additional Information

CASE (Transact-SQL)

RAND (Transact-SQL)

CRYPT_GEN_RANDOM (Transact-SQL)

NEWID (Transact-SQL)

NEWSEQUENTIALID (Transact-SQL)

Example Test SQL

DECLARE  @c INT = 5

SELECT  CASE CONVERT( SMALLINT, RAND() *@c)
            WHEN 1 THEN 'a'
            WHEN 2 THEN 'b'
        END

DECLARE  @test SMALLINT = CONVERT( SMALLINT, RAND() *@c)

SELECT  CASE @test
            WHEN 1 THEN 'a'
            WHEN 2 THEN 'b'
        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

Analysis Rules