conditional where clause - 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 2000 Forums

Transact-SQL (2000)

conditional where clause

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-01-18 : 02:46:04

Hi,

I have a sp with a where clause. I have a screen in .Net where the user has to select whether he wants to see old or new students.
If he selects old students i have to say "where student.rdate>'01/01/2005'" . If he selects new students then
where "student.rdate>'01/01/2005'"
I thought of doing something like the following but it gives me errors:

select sName,sLastname from student
where
sid=@sid
and
if @param=old
begin
student.rdate>'01/01/2005'
end
if @param=new
begin
student.rdate<'01/01/2005'
end

How can i write a conditional where clause?

Thanks

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-18 : 03:25:11

select sName,sLastname from student
where
sid=@sid
and
(
(@param=old and student.rdate>'01/01/2005')
or
(@param=new and student.rdate<'01/01/2005')
)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-01-18 : 03:42:12

Thanks for the reply.
I tried your example but for some reason once i add that condition i don't get any results in the resultset and when I delete the conditon i do get results. I also checked to see if there are students with rdate> or < then the date specified and there are.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-01-18 : 17:37:39

Double-check the < and > symbols to make sure they are set correctly with the old or new parameter. Also, if @param is passed in as the literal words old or new, then you need to put them in single quotes like:

(@param = 'old' and student.rdate < '01/01/2005')...

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-19 : 15:37:00

Stoad's solution will work just fine. However, you could avoid a possible tablescan (if your table is indexed) by eliminating the "OR" with something like this:

declare @student Table
(FirstName varchar(50)
,LastName varchar(50)
,rdate datetime)
insert @student values('oldFirstName', 'oldLastName', '1/1/1999')
insert @student values('newFirstName', 'newLastName', '2/1/2005')

declare @parm varChar(10)
--set @parm = 'new'
set @parm = 'old'

SElect @parm [@parm]
,LastName
,FirstName
,rdate
From @Student
Where 1=1
AND rdate > case when @parm = 'new' then '1/1/2005' else dateAdd(day,-1,rdate) End
AND rdate < case when @parm = 'old' then '1/1/2005' else dateAdd(day,1,rdate) end

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2005-01-19 : 16:16:05

TG thanks for the code but I couldnt understand why to use 1=1 in where clause.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-19 : 18:01:34

No sweat, saglamtimur.
It serves no purpose. It's there just to simulate your sid=@sid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources