Selecting a random string from a list results in column being populated with NULLS [SQL]

I am trying to populate a column in sql by replacing old values with new values from a list.

SELECT CHOOSE(ROUND(RAND() * 3, 1) + 1,'Bill','Steve','Jack')

Running this query gives me instances of NULLS which i don’t want, why am i getting NULLS?

Answer

CHOOSE is just shorthand for a CASE expression. I talk about how an expression like RAND() can be evaluated more than once here (see the section “Expressions can be evaluated more than once”). I also talk about this here (search the page for “RAND()“).

Basically, what happens is CHOOSE(expr, a, b) gets expanded to:

CASE
  WHEN expr = 1 THEN a
  WHEN expr = 2 THEN b
  ELSE NULL
END

Each time you see expr there, it can be evaluated again. So the first time you call RAND() it could yield 2, then it moves to the next WHEN, and this time it could yield 1, then all that’s left is NULL.

The solution is to generate RAND() just once, e.g.

DECLARE @rand tinyint = CONVERT(tinyint, (RAND()*3) + 1);

SELECT CHOOSE(@rand,'Bill','Steve','Jack');