August 3, 2011 at 8:17 pm
Hi Team,
Need urgent help.
Am trying to implement searching logic in my application.
I can go for IF condition and dynamic sql inside my stored procedure but am really looking is , is there any way to manipulate the
where condition using CASE. Basically i am trying achieve the below. If am able to do this, then i can implement the same in my
actual search based on 15 columns.
create table sample
(branchcode varchar(10),
promocode varchar(10),
effdt datetime,
exprdt datetime
)
insert into sample
select 'b1','p1','2001-01-01','2002-01-01'
union all
select 'b2','p2','2001-01-01','2002-01-01'
union all
select 'b3','p3','2005-01-01','2007-01-01'
union all
select 'b4','p4','2011-01-01','2012-01-01'
union all
select 'b4','p4','2011-01-01','2012-01-01'
exec usp_p2 'b1',null,null,null
exec usp_p2 'b1','p1',null,null
create proc usp_p2
@branchcode varchar(10),
@promocode varchar(10)
as
begin
select * from sample
where (1=1)
and branchcode = @branchcode
and (case @promocode is null then '1' else 'promocode' end) = (case when @promocode is null then '1' else @promocode end)
end
if i pass both the parameters (@branchcode and @promocode) then the condtion should be evaluated as below
where (1=1)
and branchcode = @branchcode
and promocode = @promocode
if i pass only the branch code then the condition should be formed something like below as boolean expression
where (1=1)
and branchcode = @branchcode
and 1 = 1
I have options to go for 'IF' and dynamic sql but am looking for something using CASE statement.
Using such logic i want to implement SEARCHING in a efficient way.
Is there any way to implement such logic using T-SQL ?
Any help would be greatly appreaciated.
Thanks in advance.
August 3, 2011 at 9:27 pm
resolved my self.
For the benefit of others. Hope this will be useful.
alter proc usp_p1
@branchcode varchar(10),
@promocode varchar(10),
@effdt datetime,
@exprdt datetime
as
begin
if (@branchcode is null and @promocode is null and @effdt is null and @exprdt is null)
begin
return(0)
end
select * from sample
where (1=1)
and (branchcode = @branchcode or @branchcode is null)
and (promocode = @promocode or @promocode is null)
end
August 3, 2011 at 9:36 pm
How about this?
create proc usp_p2
@branchcode varchar(10),
@promocode varchar(10)
as
begin
select * from sample
where (1=1)
and branchcode = ISNULL(@branchcode , branchcode )
and promocode = ISNULL ( @promocode , promocode )
end
Seemed straight forward to me 🙂
August 3, 2011 at 9:47 pm
Hi,
There is a slight difference 🙂
using my implementation if i execute
exec usp_p1 'b1',null,null,null
-- output which is expected.
/*
b1p12001-01-01 00:00:00.0002002-01-01 00:00:00.000
*/
using ur implementation
exec usp_p1 'b1',null,null,null
-- no output. which is not expected.
However, thanks for the reply. 🙂
August 3, 2011 at 9:52 pm
Oracle_91 (8/3/2011)
Hi,There is a slight difference 🙂
using my implementation if i execute
exec usp_p1 'b1',null,null,null
-- output which is expected.
/*
b1p12001-01-01 00:00:00.0002002-01-01 00:00:00.000
*/
using ur implementation
exec usp_p1 'b1',null,null,null
-- no output. which is not expected.
However, thanks for the reply. 🙂
why because, i get only 2 values in parameter list. increase it to any number and add as many in the AND clause in the SP.. you ll get what you want..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply