Home

Description

The CASE input expressions should be invariant rule states that when using the CASE statement in SQL Server, the input expressions should remain the same throughout the statement. This means that any variables used in the input expressions should not be changed or modified, as this could lead to unexpected results. This rule is important to ensure that the CASE statement produces the expected output.

Key Benefits

• Improved Code Quality: CASE input expressions should be invariant to ensure that the code is as clean and concise as possible.
• Easier to Read: CASE input expressions should be invariant to make the code easier to read and understand.
• More Reliable: CASE input expressions should be invariant to make the code more reliable and less prone to errors.
• More Efficient: CASE input expressions should be invariant to make the code more efficient and reduce the amount of time it takes to execute.

Non-compliant Code Example

SELECT 
    CASE CRYPT_GEN_RANDOM(20)  --Non compliant code
           WHEN 5 THEN 'Number 5'
           ELSE 'Unknown Number'
        END,
    CASE CONVERT(INT, RAND()*@temp)  --Non compliant code
           WHEN 8 THEN 'Number 8'
           ELSE 'Unknown Number'
        END,
    CASE NEWID()  --Non compliant code
           WHEN 2 THEN 'Number 2'
           WHEN 6 THEN 'Number 6'
           ELSE 'Unknown Number'
        END
 FROM   RandomTable

Compliant Code Example

Declare @a int;
Declare @rand1 int = CRYPT_GEN_RANDOM(20);   --Compliant code
Declare @rand2 int = CONVERT(INT, RAND()*@temp);   --Compliant code
Declare @rand3 int = NEWID();   --Compliant code

Select 
    CASE @rand1     --Compliant code
         WHEN 5 THEN 'Number 5'
         ELSE 'Unknown Number'
        END,
    CASE @rand2   --Compliant code
         WHEN 8 THEN 'Number 8'
         ELSE 'Unknown Number'
        END,
    CASE @rand3  --Compliant code
         WHEN 2 THEN 'Number 2'
         WHEN 6 THEN 'Number 6'
         ELSE 'Unknown Number'
        END
From RandomTable;