Interesting facts about Case statement in SQL Server--- Creating the table Venkat
drop table venkat
create table venkat(id int, val int ,date datetime,nam varchar(10))
insert into venkat values(1,200,'3/2/2008','venka')
insert into venkat values(1,100,'10/2/2008','venk')
insert into venkat values(3,400,'10/2/2008','arun')
insert into venkat values(3,500,'10/4/2008','balu')
insert into venkat values(3,600,'10/4/2008','lakshmi')
insert into venkat values(3,700,'10/4/2008','santhi')
--------------------------------------------------------
select * from venkat
--------------------------------------------------------Case statement is used to check the alternatives.
"If the value is this much, show it as this. Else show it as this."
--------------------------------------------------------
-- A simple select statement for you
select id, value =
case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end
from venkat--------------------------------------------------------
--------------------------------------------------------
-- Complication in case statement
-- If i need to select two values based on one value. Then in this case, we need to write two case statement as below.
-- Instead of one case statement.
select id,
case
when val=100 then value ='One Hundred' and val = 'One'
when val=200 then value ='Two Hundred' and val = 'Two'
when val=400 then value ='Four Hundred' and val = 'Four'
when val=500 then value ='Five Hundred' and val = 'Five'
when val=600 then value ='Six Hundred' and val = 'Six'
when val=700 then value ='Seven Hundred' and val = 'Seven'
end
from venkat--------------------------------------------------------
-- The above statement will throw error.
--------------------------------------------------------
select id, value =
case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end,
val=
case
when val=100 then 'One'
when val=200 then 'Two'
when val=400 then 'Four'
when val=500 then 'Five'
when val=600 then 'Six'
when val=700 then 'Seven'
end
from venkat
--------------------------------------------------------
-- My condition is to create a procedure with order by a column.
alter procedure venkatesanprabu_proc
(@options int)
as
begin
select id, value =
case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end
from venkat
order by
case
when @options=1 then id
when @options=2 then nam
end
end
--------------------------------------------------------
-- Case statement in Order by clause
--------------------------------------------------------
-- The above statement will throws an error.
-- An interesting fact in SQL Server is, we can't use different data type in the order by clause.
-- In the above statement, If it's option=1 then output will be order by id
-- Else if the option is equal to 2 then output will be order by nam.
--------------------------------------------------------
-- Here is the solution for the above problem. We need to use two case statement to resolve it.
--------------------------------------------------------
alter procedure venkatesanprabu_proc
(@options int)
as
begin
select id, value =
case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end
from venkat
order by
case
when @options=1 then id
end,
case
when @options=2 then nam
end
end
--------------------------------------------------------
exec venkatesanprabu_proc 1
exec venkatesanprabu_proc 2
--------------------------------------------------------
Thanks and Regards,
Venkatesan Prabu .J
