searching, is this possible ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

searching, is this possible ?

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-08-03 : 22:19:05

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.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-04 : 06:58:08

If I've understood you correctly, you want to search on branchcode every time, but only on promocode if a search term is entered. If the promocode parameter is null, it is ignored.

If so, try this:

declare @branchcode varchar(10)
declare @promocode varchar(10)
set @branchcode = 'b1'
set @promocode = 'p1'

select * from #sample
WHERE
(@branchcode = branchcode)
AND (@promocode = promocode OR @promocode IS NULL)

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources