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

Advertisements

6 comments

  1. hungster · · Reply

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

  2. Will u post some simple query using case statement….

  3. Want it in a simple manner…

  4. Nice one but please give the simplest one

  5. it is so defecult code

    1. what is difficult in that code
      that is so simple

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s