Problem with simple cursor

[Jimbo]

I have a cursor here that appears to never stop running even though the
record set that populates it is only 22 records

heres the code:

declare cursorfinal cursor for
select appointmenteffdate, appointmentDuration, provideroid from @main

open cursorfinal

FETCH NEXT FROM cursorfinal
INTO @aff, @duration, @poid

WHILE @@FETCH_STATUS = 0
BEGIN

delete from @main_temp where (appointmenteffdate between @aff and
dateadd(minute, @duration , @aff) or
dateadd(minute,appointmentduration,appointmenteffd ate ) between @aff
and dateadd(minute, @duration , @aff))
and provideroid = @poid
select @poid

END

CLOSE cursorfinal
DEALLOCATE cursorfinal

[Erland Sommarskog]

Jimbo (ji********@motorola.com) writes:

I have a cursor here that appears to never stop running even though the
record set that populates it is only 22 records

heres the code:

declare cursorfinal cursor for
select appointmenteffdate, appointmentDuration, provideroid from @main

open cursorfinal

FETCH NEXT FROM cursorfinal
INTO @aff, @duration, @poid

WHILE @@FETCH_STATUS = 0
BEGIN

delete from @main_temp where (appointmenteffdate between @aff and
dateadd(minute, @duration , @aff) or
dateadd(minute,appointmentduration,appointmenteffd ate ) between @aff
and dateadd(minute, @duration , @aff))
and provideroid = @poid
select @poid

END

CLOSE cursorfinal
DEALLOCATE cursorfinal

You need to move the FETCH into the loop:

WHILE 1 = 1
BEGIN
FETCH ...
IF @@fetch_status <> 0
BREAK

-- Do stuff
END

However, there is no reason to write a cursor at all here:

delete @main_temp
from @main_temp t
where EXISTS
(SELECT *
FROM @main m
WHERE m.provideroid = t.provideroid
AND (t.appointmenteffdate between
m.appointmenteffdate and
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)
or dateadd(minute, t.appointmentduration,
t.appointmenteffdate)
between m.appointmenteffdate AND
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)))

It's essential that you lear to operations like this in a set-based
fashion. Maybe your cursor over 22 rows runs quickly, but what if
real production data has 20000 rows? The difference beween the
cursor and a set-based statement like the above, can easily be a
factor of 1000 in such case.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

Here's a guess. I'm assuming that (provideroid, appointmenteffdate) is
unique in @main_temp. If you need more help then please post DDL /
DECLARE statements so that we don't have to guess at keys and
constraints - that's pretty important information for solving data
manipulation problems.

DELETE T
FROM @main_temp AS T
WHERE EXISTS
(SELECT *
FROM @main AS M
WHERE M.provideroid = T.provideroid
AND M.appointmenteffdate
< DATEADD(MINUTE,T.appointmentduration,T.appointment effdate)
AND DATEADD(MINUTE,M.appointmentduration,M.appointment effdate)

T.appointmenteffdate

AND M.appointmenteffdate < T.appointmenteffdate) ;

(untested)

Alternatively, why bother deleting the rows at all from the table
variable? Just ignore the unwanted rows until you discard the variable.

--
David Portas
SQL Server MVP
--

[Jimbo]

The reason why I did the cursor is really complicated...I tried it with
the delete before and it wasnt working on certain records...but the
cursor solved it..thanks for all your help and suggestions..got it
working