T-SQL Query gives different results when declaring integer and calculating in a query
So, I was experimenting with generating SQL random numbers in a query and I noticed something strange.
Let's say I run the following query:
declare @Random int = CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1;
select CHOOSE(@Random,'One','Two','Three','Four','Five')
SQL random number gen is a bit cumbersome, but the basic idea here is simple - pick a random number between 1 and 5, then display that number as text in the select box. This works as expected.
However, if I accept the SAME request, but insert the random number formula into the method instead of declaring it as an integer, so it's all on one line:
select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
1,'One','Two','Three','Four','Five')
I still get values from one to five when I run the query, but sometimes I also get NULL. NULL comes up quite often, about once every 5 times. If I put both requests in ssms and run them next to each other multiple times, I often see a null value for the second request, but the first is never NULL.
So why is that? Isn't this the same calculation? I don't know why these two queries gave different results, but I feel like I can learn something useful about T-SQL by learning.
Any experts want to enlighten me?
source to share
This is a very subtle feature issue choose
in SQL Server (well, Microsoft probably considers it a feature). The function is indeed shorthand for expression case
. So your expression:
select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
1,'One','Two','Three','Four','Five')
converts to:
select (case when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 1 then 'One'
when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 2 then 'Two'
when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 3 then 'Three'
when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 4 then 'Four'
when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 5 then 'Five'
end)
This means it newid()
is called multiple times. This is usually not the behavior you want.
source to share