-
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.
-
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
-
What about this
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.
-
You can!
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
ThanQ guys for ur replies.
-
It's Working
The solution by k2kataria solved my problem .Thanq very much for ur solution and thanks for ur good logic
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|