asked on

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

ASKER CERTIFIED SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lynnton

ASKER

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

lynnton

ASKER

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.

lynnton

ASKER

ptjcb,

I'll repost and hope other expert will join.

Thanks.