rand Functions in case Expressions

Expressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certain case expressions.

For example, the SQL standard specifies that case expressions with this form:

case expression
	when value1 then result1
	when value2 then result2
	when value3 then result3
...
end

are equivalent to this form of case expression:

case expression
	when expression=value1 then result1
	when expression=value2 then result2
	when expression=value3 then result3
...
end

This definition explicitly requires that the expression be evaluated repeatedly in each when clause that is examined. This definition of case expressions affects case expressions that reference functions such as the rand function. For example, this case expression:

select
CASE convert(int, (RAND() * 3))
		when 0 then "A"
		when 1 then "B"
		when 2 then "C"
		when 3 then "D"
	else "E"
end

is defined to be equivalent to this one, according to the SQL standard:

 select
CASE
		when convert(int, (RAND() * 3)) = 0 then "A"
		when convert(int, (RAND() * 3)) = 1 then "B"
		when convert(int, (RAND() * 3)) = 2 then "C"
		when convert(int, (RAND() * 3)) = 3 then "D"
	else "E"
end

In this form, a new rand value is generated for each when clause, and the case expression frequently produces the result “E”.