Stored Procedure Help Please - 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)

Stored Procedure Help Please

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-26 : 08:38:23

I have a stored procedure that does a insert into query. I need to conditionally change the where clause if m.prestnstyle='Be Back'. This query typically inserts 200 lines a week, with about 1 in 1000 being a 'Be Back'. Any help is much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 08:40:33

quote:
I need to conditionally change the where clause if m.prestnstyle='Be Back'

What is the condition ? You can use CASE ... WHEN . . to do that

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 08:41:31

Can you post the full query?
Also what do you want to do if m.prestnstyle='Be Back'?

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-26 : 08:45:11

quote:
Originally posted by madhivanan

Can you post the full query?
Also what do you want to do if m.prestnstyle='Be Back'?

Madhivanan

Failing to plan is Planning to fail

my boss wont all me to post the full query but here is the where clause

if m.prestnstyle = 'Be Back'

where ((m.apptdate BETWEEN w.TeamStartDate AND w.TeamEndDate))
and
(((m.prestnstatus='S') and (m.prestndate between @begin and @end))
or ((m.prestnstatus='OH') and (m.outofholddate between @begin and @end)))
end

if m.prestnstyle <> 'Be Back'

where ((m.prestndate BETWEEN w.TeamStartDate AND w.TeamEndDate))
and
(((m.prestnstatus='S') and (m.prestndate between @begin and @end))
or ((m.prestnstatus='OH') and (m.outofholddate between @begin and @end)))
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 08:50:13

Try

where (
		(m.prestnstyle = 'Be Back' and m.apptdate BETWEEN w.TeamStartDate AND w.TeamEndDate)
		or
		(m.prestnstyle <> 'Be Back' and m.prestndate BETWEEN w.TeamStartDate AND w.TeamEndDate)) 
and
(((m.prestnstatus='S') and (m.prestndate between @begin and @end))
or ((m.prestnstatus='OH') and (m.outofholddate between @begin and @end)))

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-08-26 : 08:58:56

quote:

Originally posted by madhivanan

Try

where (
		(m.prestnstyle = 'Be Back' and m.apptdate BETWEEN w.TeamStartDate AND w.TeamEndDate)
		or
		(m.prestnstyle <> 'Be Back' and m.prestndate BETWEEN w.TeamStartDate AND w.TeamEndDate)) 
and
(((m.prestnstatus='S') and (m.prestndate between @begin and @end))
or ((m.prestnstatus='OH') and (m.outofholddate between @begin and @end)))

Madhivanan

Failing to plan is Planning to fail

thanks it worked just as I suspected.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 09:06:12

quote:
Originally posted by blackX

quote:
Originally posted by madhivanan

Try
where (
(m.prestnstyle = 'Be Back' and m.apptdate BETWEEN w.TeamStartDate AND w.TeamEndDate)
or
(m.prestnstyle <> 'Be Back' and m.prestndate BETWEEN w.TeamStartDate AND w.TeamEndDate))
and
(((m.prestnstatus='S') and (m.prestndate between @begin and @end))
or ((m.prestnstatus='OH') and (m.outofholddate between @begin and @end)))

Madhivanan

Failing to plan is Planning to fail

thanks it worked just as I suspected.

suspected or expected?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources