CODEWITHSUNDEEP
sqlsql-servert-sql
Mat Richardson
Mat Richardson

Reputation: 3606

Unexpected rand() behaviour?

The TSQL below is supposed to randomly generate 20 rows containing an incrementing ID and a day of the week. See this in action here.

Note: I know this code is already flawed I'm just playing around with an idea.

declare @loop int = 1;
if OBJECT_ID('tempdb..#temp') is not null drop table #temp;
create table #temp(id int, dayofweek varchar(10))

while @loop < 21
begin
    insert into #temp
    values(@loop, case ceiling(rand()*7)
                    when 1 then 'Monday'
                    when 2 then 'Tuesday'
                    when 3 then 'Wednesday'
                    when 4 then 'Thursday'
                    when 5 then 'Friday'
                    when 6 then 'Saturday'
                    when 7 then 'Sunday'
                end)
    set @loop += 1
end

If I do select * from #temp I get some NULL values in my query results for the 'dayofweek' column. Can anybody explain why this is? I've looked at the return results of ceiling(rand()*7) and as far as I can tell it would only ever return results between 1 and 7.

What am I missing?

Upvotes: 3

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

This is very subtle. The problem is that the case expression is being evaluated once for each comparison. So, sometimes all the comparisons will fail, because they are using different numbers.

Buried in the documentation is this note:

  • Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.

This is all the more surprising, because rand() doesn't usually evaluate multiple times (in a select, set, or where clause at least). In this case, it seems to be. Fortunately, there is an easy fix for you:

declare @loop int = 1;
declare @dow int;
if OBJECT_ID('tempdb..#temp') is not null drop table #temp;
create table #temp(id int, dayofweek varchar(10))

while @loop < 21
begin
    set @dow = ceiling(rand()*7);

    insert into #temp
    values(@loop, case dow
                    when 1 then 'Monday'
                    when 2 then 'Tuesday'
                    when 3 then 'Wednesday'
                    when 4 then 'Thursday'
                    when 5 then 'Friday'
                    when 6 then 'Saturday'
                    when 7 then 'Sunday'
                end)
    set @loop += 1;
end;

Upvotes: 7

Related Questions