Home

CASE input expressions should be invariant

Rule description

  • CASE input expressions should be invariant

 

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;
Visual Expert 2020
 VETSQLRULE4