Case statement in where clause

can i use case statement in where clause.
The scenario is as follow

declare @param int
select
*
from
table1
where
column1 = 'asdf'
column2= @param

In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.


can i use case in where clause so that if @param is '' then i will not include in the where clause

why cant your use IF .....begin....end conditions

You can't use CASE in WHERE clause.

Not true. You can use Case in the Where clause

Select * from table
where
1 = CASE
When column1 = 'asdf' then 1
When column2= @param then 1
Else 0
END

it will not work.

gvraghuramraju wants to ignore 'and column2 =@param' when @param=''

create table table1 (column1 varchar(10), column2 int)
insert into table1 select 'asdf',1
insert into table1 select 'asdf1',2
insert into table1 select 'asdf',NULL


For example if @param is NULL he wants the statement to be like this

select * from table1 where column1 = 'asdf'


For example if @param is not NULL he wants the statement to be like this

select * from table1 where column1 = 'asdf' and column2 = @param

Select * from table
where 1= CASE
when @param is null and column1='asdf' then 1
when @param is not null and column1='asdf' and colummn2 = @param then 1
else 0
END

Just an idea. Syntactically, the statement is corrent but you may alter the statement to address the requirements.

In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.

The condition above where @param is '' or not to be included is same as having a default value and having an OR condition

select
*
from
table1
where
column1 = 'asdf'
column2= @param

Can be written as


select
*
from
table1
where
column1 = 'asdf' And ((column2= @param) Or column2='')

In the above statement if u work out the logic table
If value in @param is equal to column2 then it evaluates to
((column2 = @param) Or column2='')
---True--- Or ---False---
The return value of the whole expression is true

In the condition where @param is '' then the same expression again evaluates to True. there fore will not affect the overall logic of the where clause.

But if @param is something other than column2 and is not equal to '' then the expression evaluates to false.

and ur requirement is satisfied. Let me know if u understand the logic.

Ketan

Maybe a stupid idea but:

declare @param int,@command char(255)

{...}

if @param <> ''
select &command = 'select * from table1 where column1 = ''asdf'' and column2 = ' + convert(char(10),@param)
else
select &command = 'select * from table1 where column1 = ''asdf''


exec (@command)


This should work if I did not make a big mistake.

I already answered it on the begining of this thread.

ThanQ guys for ur replies.

The solution by k2kataria solved my problem .Thanq very much for ur solution and thanks for ur good logic