[RESOLVED] SP problem - 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)

[RESOLVED] SP problem

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-23 : 12:22:30

Hi,
I am trying to get my SP to work with the following


@StartDate  Varchar(20),
@EndDate Varchar(20)

Select * from mytable where mydate => @Startdate and myDate <= @EndDate

The above is fine, however I need to insert a condition in between this statement as follows:

if the field mytable.status = 'CLOSED' then I want to substitute mydate with mydateClosed.

basically, depending on the value of Mytable.status I need to change the SELECT statement from mydate to mydate1

is there an easy way to do this?

thanks
Craig.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-23 : 12:44:42

yes.. you can use CASE in your WHERE clause to accomplish this..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-23 : 12:51:04

Hi,
I dont think that will work, the "Select" statement has to be dymanic based on the value of mytable.status.

ie. if status = closed then

select * from mytable where mydate between @startdate and @enddate

else

select * from mytable where MYDATE1 between @startdate and @enddate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-23 : 12:52:06

[code]
SELECT * FROM
(
Select *,case when status = 'CLOSED' then mydateClosed else mydate end as DateVal from mytable
)t
where DateVal => @Startdate
and DateVal <= @EndDate
[/code]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-24 : 07:10:38

Thanks , I've got it working now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources