Daily Archives: May 3, 2015


Mutable logical condition in CASE expression 6

In our previous blog post Different data types in case expression, we have explored the behavior of CASE expression in case of different data types in “THEN” and “ELSE” part. Now in this post “Mutable logical condition in CASE expression”, we are going to unfold the behavior of CASE expression when we have a non deterministic function in logical expression (WHEN part).

Mutable logical condition in CASE expression

Have a look on the below query:

--Query 1
SELECT
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN 'Case 0'
WHEN 1 THEN 'Case 1'
WHEN 2 THEN 'Case 2'
END

 

In above query, logical expression “ABS(CHECKSUM(NEWID())) % 3” of CASE expression, has a non deterministic function NEWID() wrapped inside a CHECKSUM function to generate some random number on fly. Going further the CHECKSUM value has also wrapped inside a ABS function which insures a positive number. Finally there is a … More