variable vs manual in store procedure (gurus please kindly help)

Hi

We have a store procedure..

The SP is very fast, takes 1:30 minutes to finish if I place a manual date
i.e.
dtstart between  '3/31/2005' and  '4/1/2005'
_____________________________________________________________
unfortunately when we place a variable for previousday and presentday
Declare Presentday as datetime
Declare Previousday as datetime

select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )
_____________________________________________________________

it's already 3 hours and still running with no results..

Please kindly guide me what could be wrong? Is there any difference between using manual and variable?

DTStart is datetime datatype
i.e.
2004-09-22 00:00:05.000


Thanks.


One of the experts consulted this approach, unfortunately the results were the same.

CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay datetime,
@PresentDay datetime
)
AS
select * from table1 where date between @previousday and @presentday
Go

CREATE PROCEDURE sproc_UpdateTemp
AS
DECLARE @PreviousDay datetime
DECLARE @PresentDay datetime
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )
exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay
Go
LVL 1
lynntonAsked:
Who is Participating?
 
ptjcbConnect With a Mentor Commented:
It sounds as if you have an infinite loop. Can you post the query?
0
 
lynntonAuthor Commented:
ptjcb,

Please see below, but it does work on manual entry.. is there anything different from running SP vs inline SQL using QA?

Thanks.

CREATE PROC [dbo].[sproc_UpdateTemp_Sub]

@Presentday as datetime,
@Previousday as datetime
as

select operatorid,messagetype,workstation,workstationtime
into #tempworkstation2
from [1.1.1.1].dbo.workstation
where workstationtime between @previousday and @presentday
and (messagetype ='workstation login' or  messagetype ='workstation logout')


SELECT m.operatorid, m.workstation, m.workstationtime as login,
(SELECT TOP 1 workstationtime  FROM #tempworkstation2 s WHERE s.workstation = m.workStation AND s.workstationtime > m.workstationtime ORDER BY workstationtime ASC) AS logout
INTO #tempresult
FROM #tempworkstation2 m
WHERE messagetype='Workstation Login'
AND EXISTS
(SELECT 1  FROM #tempworkstation2 s WHERE s.workstation = m.workStation AND s.workstationtime > m.workstationtime AND s.messagetype = 'Workstation Logout')


select b.teamleader as [team],b.name,a.*
from #tempresult a
left join team b on a.operatorid=b.operatorid order by a.operatorid,b.teamleader

Go
0
 
lynntonAuthor Commented:
ptjcb,

I forgot, I place manual data in the SP and it run fine 1:20 minutes to generate the result.

it's a datatype ?

Thanks.
0
 
lynntonAuthor Commented:
ptjcb,

I'll repost and hope other expert will join.

Thanks.
0
All Courses

From novice to tech pro — start learning today.