?
Solved

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

Posted on 2005-04-01
4
Medium Priority
?
292 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:lynnton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 2000 total points
ID: 13681891
It sounds as if you have an infinite loop. Can you post the query?
0
 
LVL 1

Author Comment

by:lynnton
ID: 13682278
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
 
LVL 1

Author Comment

by:lynnton
ID: 13682749
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
 
LVL 1

Author Comment

by:lynnton
ID: 13683389
ptjcb,

I'll repost and hope other expert will join.

Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question