DotNet Friends

August 8, 2008

use case in where clause in sqlserver 2005

steps:

–declare temp table
declare@tbl as table(id int,username varchar(50),Age int
)
–insert data into temp table
insert into @tbl
values
(1,‘Amit’,10
) 

insert into @tbl
 

insert into @tbl

values
(1,‘Bhavin’,10
) 

insert into @tbl
 

insert into @tbl

values
(1,‘Chirag’,10
) 

insert into @tbl
 

insert into @tbl

values
(1,‘Dhaval’,10
) 

insert into @tbl
 

insert into @tbl

values
(1,‘Nirav’,10
) 

insert into @tbl
 

insert into @tbl

values
(1,‘kumar’,10
)

–declare variable
declare @username varchar(50
)
set @username =
‘a’ 

select * from @tbl  

select * from @tbl

where
case
@username
     whennullthen1
     when‘a’
then
          case when
username like‘a%’then1 else0
end
     when
‘b’
then
            case when
username like‘b%’then1 else0
end
end
=1

1 Comment »

  1. Thanks for providing a good, short working script to show how CASE WHEN works.

    Comment by hungster — August 26, 2009 @ 12:40 am


RSS feed for comments on this post. TrackBack URI

Leave a comment

Blog at WordPress.com.