Home > Database, SqlServer > use case in where clause in sqlserver 2005

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. hungster
    August 26, 2009 at 12:40 am | #1

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

  2. April 12, 2011 at 12:37 pm | #2

    Will u post some simple query using case statement….

  3. April 12, 2011 at 12:38 pm | #3

    Want it in a simple manner…

  4. April 27, 2011 at 4:13 pm | #4

    Nice one but please give the simplest one

  5. September 12, 2011 at 8:08 am | #5

    it is so defecult code

    • September 12, 2011 at 8:10 am | #6

      what is difficult in that code
      that is so simple

  1. No trackbacks yet.

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 )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 244 other followers