Stored procedure - cursor problem

[Naoise]

I have three tables

tblOffer : OfferID | OfferDate | OfferContent
tblOfferImages : OfferID | ImageID |
tblImages : ImageID | ImageName |

so tblOfferImages just links tblOffer and tblImages.

I am writing a stored procedure to delete a special offer record from all these tables. There is a FK relationship between tblOffer/tblImages to tblOfferImages. Therefore I must delete from tblOfferImages first. I delete from tblOfferImages the tblOffer, I have created a cursor that
lists all ImageIDs in tblOfferImages that have the particular OfferID before I delete any info from tblOfferImages. The problem is that my cursor does not seem to work. It does not delete anything from tblImages. When I debug printing out @@fetch_status, it returns -1 which means that there is an error in the SQL statment that set up the cursor but when I run that SELECT statement on its own it returns results. Any ideas???

**APOLOGIES FOR FORMATTING DIFFERENCES**

CREATE PROCEDURE spDeleteOffer
(
@OfferID INT
,@SQLMessage VARCHAR(1000) OUTPUT
)
AS
BEGIN

DECLARE @ImageID INT


BEGIN TRANSACTION

--This is where the problem is, I think.
DECLARE image_cursor cursor for
SELECT ImageID
FROM tblOfferImages
WHERE OfferID = @OfferID

--This works fine
delete from tblOfferImages where OfferID = @OfferID

IF(@@Error <> 0)
BEGIN
SET @SQLMessage = 'Failed to delete Offer' ROLLBACK TRANSACTION
RETURN 1
END
ELSE
BEGIN

--This works fine
delete from tblOffer where OfferID = @OfferID

IF(@@Error <> 0)

BEGIN
SET @SQLMessage='Failed to delete Offer' ROLLBACK TRANSACTION
RETURN 1
END
ELSE

BEGIN
--Delete all images for this offer
--This does not work, it deletes nothing

OPEN image_cursor
FETCH NEXT FROM image_cursor INTO @ImageID
WHILE @@fetch_status = 0
BEGIN
delete from tblImages where ImageID = @ImageID
END
FETCH NEXT FROM image_cursor INTO @ImageID

CLOSE image_cursor
DEALLOCATE image_cursor

IF(@@Error <> 0)
BEGIN SET @SQLMessage = 'Failed to delete Offer' ROLLBACK TRANSACTION RETURN 1
END

ELSE
BEGIN SET @SQLMessage='Offer deleted successfully' COMMIT TRANSACTION RETURN 0
END
END
END

END
GO

 

[SQLSister]

Why are you doing this in a cursor?

delete from tblImages where ImageID In (SELECT ImageID 
FROM tblOfferImages WHERE OfferID = @OfferID)

delete from tblOfferImages where OfferID = @OfferID

Incidentally why your cursor isn't deleting any records is because you already deleted all the records from the tblOffer images, so the select for the cursor now has no records.


Questions about posting. See faq183-874