sql server - T-SQL Query Gives Different Results when declaring integer vs calculating in-query -

so i've been experimenting sql random number generation in query, , noticed odd.

let's run following query:

declare @random int = cast(rand(checksum(newid())) * 5 int) + 1;  select choose(@random,'one','two','three','four','five') 

sql random number gen little bulky, basic idea here simple - pick random number between 1 , 5, display number text select window. works expected.

however, if take same query, paste random number formula method instead of declaring integer, it's on 1 line:

select choose(cast(rand(checksum(newid())) * 5 int) +     1,'one','two','three','four','five') 

i still values 1 5 when run query, null. null comes pretty often, 1 in 5 times. if put both queries ssms , run them next each other few times, see null value second query, first never null.

so why that? aren't these same calculation? have no idea why these 2 queries give different results, feel can learn useful t-sql finding out.

any experts want enlighten me?

this subtle problem choose function in sql server (well, microsoft considers feature). function shorthand case expression. so, expression:

select choose(cast(rand(checksum(newid())) * 5 int) + 1,'one','two','three','four','five') 

is translated into:

select (case when cast(rand(checksum(newid())) * 5 int) + 1 = 1 'one'              when cast(rand(checksum(newid())) * 5 int) + 1 = 2 'two'              when cast(rand(checksum(newid())) * 5 int) + 1 = 3 'three'              when cast(rand(checksum(newid())) * 5 int) + 1 = 4 'four'              when cast(rand(checksum(newid())) * 5 int) + 1 = 5 'five'         end) 

that means newid() being called multiple times. not behavior want.