**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

--------------------------------------------------------

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

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

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

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

--------------------------------------------------------

(@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

--------------------------------------------------------

(@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

exec venkatesanprabu_proc 2

--------------------------------------------------------

## No comments:

Post a Comment