Why does this SQL Case statement return a null in some instances?

Question

I'm using this script to generate some data

select case abs(checksum(newid())) % 5 + 1
                    when 1 then 'excellent'
                    when 2 then 'good'
                    when 3 then 'fair'
                    when 4 then 'poor'
                    when 5 then 'failing'
                    end

It returns these values: excellent,good,fair,poor,failing,null

I assumed I was getting a result outside of 1,2,3,4,5 causing the null to return from the case. So I executed this script:

select abs(checksum(newid())) % 5 + 1

It returns only these values: 1,2,3,4,5

Assuming I didn't get a 0 or 6 during debugging due to some exceptional statistical phenomenon (because I ran the statement > 100 times), there is some other issue causing this.

As an aside, `CASE` is an *expression*, not a *statement*. Perhaps a worthwhile read (for these and other reasons): [Dirty Secrets of the CASE Expression](http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression). — Aaron Bertrand, Aug 12 '14 at 17:14

score 4 · Answer 1 · answered Aug 12 '14 at 16:31

This occurs because SQL Server generates a new newid() for each when in the case statement. You can avoid the issue like this:

DECLARE @rand TINYINT
SELECT  @rand = ABS(CHECKSUM(NEWID())) % 5 + 1

SELECT  CASE @rand
          WHEN 1 THEN 'excellent'
          WHEN 2 THEN 'good'
          WHEN 3 THEN 'fair'
          WHEN 4 THEN 'poor'
          WHEN 5 THEN 'failing'
        END

Why does this SQL Case statement return a null in some instances?

1 Answers1