can we set multiple values in variable in sql

user_11515056 0
Viewed 79
DECLARE @a int  
SET @a = 5 

SET @a = CASE
            WHEN @a >= 5 THEN 1 
            WHEN @a <= 5 THEN 2 
            WHEN a < 10 THEN 3 
         END

SELECT @a

@a value should be 3, but why it is showing 1?

Can anyone explain how it works?

I have been asked same question one of SQL interview

sql-server sql
2 Answers

Case expression will Evaluates, as order you specified :

So, you want prioritized when clause :

case when @a < 10 then 3 
     when @a <= 5 then 2 
     when @a >= 5 then 1 
end
user_55294450

The CASE statement goes through conditions and returns a value when the first condition is met. Following two way of your problem should be solved, you can try one of them.

   declare @a int  

   set @a=5 
   set @a= case when  @a>5 then 1
   when @a<5 then 2 
   when @a <10 then 3 end 
   select @a


   set @a=5 
   set @a= case when @a <10 then 3
   when  @a>=5 then 1
   when @a<=5 then 2 end 
   select @a
user_113014750
Related