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