January 8th, 2005, 08:35 PM
|
|
Registered User
|
|
Join Date: Jan 2005
Posts: 3
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Thanks for replying. It's the strangest thing (well to me anyway) - the code works exactly as I want - problem is it will only run once. Any further calls from the same session = an empty recordset. It's because the cursor remains at the end, even though it's been closed and deallocated.
Code:
CREATE PROCEDURE dbo.getphotosbypage
@UserID int,
AS
DECLARE @pagecounter int,
@monthcounter int,
@priorDate datetime,
@photoID int,
@comments varchar(500),
@nextdate datetime,
CREATE TABLE photos_temp
(monthcount int,
photoID int,
comments varchar(500),
dateuploaded datetime)
set @monthcounter = 1
DECLARE photos_cursor CURSOR FOR
SELECT photoID,comments,dateuploaded FROM Photos
WHERE UserID = @UserID
ORDER BY Dateuploaded
OPEN photos_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM photos_cursor
INTO @PhotoID, @comments, @Nextdate
if datepart(mm,@nextdate) <> datepart(mm,@priordate) OR
datepart(yy,@nextdate) <> datepart(yy, @priordate)
BEGIN
SET @monthcounter = @monthcounter + 1
END
If @priorID <> @photoID
BEGIN
insert into photos_temp (monthcount,photoID,comments,dateuploaded)
values (@monthcounter, @photoID, @comments, @Nextdate)
END
SET @priordate = @Nextdate
SET @priorID = @photoID
END
select * from photos_temp
SET @photoID = null
SET @comments = null
SET @nextdate = null
DROP TABLE photos_temp
CLOSE photos_cursor
DEALLOCATE photos_cursor
GO
Last edited by Memnoch : January 9th, 2005 at 11:13 AM.
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
