Don’t Use Rand() function in CASE ExpressionsPosted: October 24, 2012
When you use Rand function in CASE expression, you may have unexpected results. Here is a discussion for this issue:
I have included whatI have found from Sybase documentation:
My input for the thread:
The return value NULL is a design behavior when you use Rand() function in CASE Expression (it seems it is inherited from Sybase product and my guess is a standard compliance behavior). You will get some unexpected results, in your case it is the NULL value.
You can use a variable to pass the value to use in the CASE Expression:
Declare @num int=CAST((50 * RAND() + 1) as int) SELECT CASE @num when 1 then 'New York' when 2 then 'Los Angeles' --.. ELSE 'Unknown' END