User Input Variables into Where

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)

User Input Variables into Where

Author

Topic

andrewz00
Starting Member

15 Posts

Posted - 2009-04-14 : 14:07:25

Ok, so i have a script written but im having an issue with the where statement... i want the user to be able to select donors that are either
1. only <16
2. only >=76
3. both

Please see code below.. i included my non-working attempt in the where clause to show what im trying to do...

declare @sdate datetime, @edate datetime, @region int, @age int
set @region = 3
set @age = 16 --or 76
set @sdate = '1/1/2008'
set @edate = '12/31/2009'

SELECT
D.DonorCode,
D.Din,
convert(varchar(12),D.DateDonationStart,101),
DS.DOnationSiteCOde,
DS.DonatioNSiteName,
P.PhlebotomyName,
DG.Age

from btdonation D with (nolock)
INNER JOIN drmDemoGraphics DG with (nolock)
ON D.KEyDOn = DG.KeyDOn
AND D.Deleted=0
INNER JOIN btDonationSite DS with (nolock)
ON D.DOnationSIteCode = DS.DOnatioNSiteCode
AND DS.Deleted=0
INNER JOIN btPhlebotomy P with (nolock)
ON D.PhlebotomyCode = P.PhlebotomyCode

where
D.deleted=0
and D.PhlebotomyCode in('A','D','B','F','G')
and d.DonationCOmpletionCode in('-')
and D.DateDOnationStart between @sdate and @edate
and @region=(case when @region=0 then 0 else d.regionid end)
--and @age = (case when @age = 1 then Dg.Age <16 when @age = 2 then dg.Age >=76 else DG.Age <16 or DG.Age >=76 end)

GROUP BY
D.DonorCode,
D.Din,
convert(varchar(12),D.DateDonationStart,101),
DS.DOnationSiteCOde,
DS.DonatioNSiteName,
P.PhlebotomyName,
DG.Age

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-15 : 10:08:23

It just seems like a tiny error in you syntax:

and (case when @age = 1 then Dg.Age <16 when @age = 2 then dg.Age >=76 else DG.Age <16 or DG.Age >=76 end)

I just removed the and @age= from before your case statement.

Try that, let us know if you still have problems.

andrewz00
Starting Member

15 Posts

Posted - 2009-04-15 : 10:21:07

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '<'.

what i was thinking was... could i just select this all into a temp table then write an if statement ...

something like:
if @agetype = 1 then begin select * from #tmp where age<16 end
if @agetype = 2 then begin select * from #tmp where age >=76 end
if @agetype = 3 then begin select * from #tmp where age >16 or age >=76 end
drop table #tmp

that might work? maybe?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-15 : 10:29:09

if @agetype = 1 then begin select * from #tmp where age<16 end
if @agetype = 2 then begin select * from #tmp where age >=76 end
if @agetype = 3 then begin select * from #tmp where age >16 or age >=76 end

can be written as

select * from #tmp where
(@agetype = 1 and age<16) or
(@agetype = 2 and age >=76) or
(@agetype = 3 and age >16)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources