Cursor Error , Skipping over Fetch status

[DwayneL]

My problem is that when i get the cursor open there is error because it skips over the fetch status. I now that there is reservation in the table with the number 1. It goes to the else of my fetchstatus else.



some sample database code is:
this is one record
1 - custID as int
5 - subclassID
2002-02-25 00:00:00.000 -first Night
5 -Duration
98765432 -credit card number
2003-03-25 00:00:00 -credit card expiry
John Doe - name on card
1 - reservation number
0 - canceled
Visa - type of card

the information going into the stored proc is:

declare @asd int

exec CancelReservation 1, @asd

The procedure is like this:

alter proc CancelReservation
(
@ReservationNum int=null,
@Confirmation int output

)


as



Declare @CustID as int
Declare @SubClassID as int
Declare @FirstNight as datetime
Declare @Duration as int
Declare @CreditCardNum as int
Declare @CreditCardExpiry as smalldatetime
Declare @NameOnCard as varchar(25)
Declare @TypeOfCard as varchar(10)


--open a cursor to the BankOfNumber table

DECLARE cur_Cancel CURSOR SCROLL FOR
SELECT @CustID, @SubClassID, @FirstNight, @Duration, @CreditCardNum, @CreditCardExpiry, @NameOnCard, @TypeOfCard
FROM Booking
Where Booking.ReservationNum = @ReservationNum


OPEN cur_Cancel

FETCH First FROM cur_Cancel -- get a record
INTO @CustID, @SubClassID, @FirstNight, @Duration, @CreditCardNum, @CreditCardExpiry, @NameOnCard, @TypeOfCard



if @@FETCH_STATUS <> 0 --keeping doing if no errors
BEGIN -->begin of if
--do stuff blah blah
END

else
BEGIN
--no record so the resev number given to proc doesn't exist
raiserror 77777 'Reseravtion does not exist'
return 0
END

CLOSE cur_Cancel
DEALLOCATE cur_Cancel

return 1


Regards

DwayneL

 

[jhall156]

@@FETCH_STATUS = 0 if the fetch was successful, -1 if you've gone beyond the last record, -2 if the fetched row is missing. JHall

 

[bperry]

Correct me if I am wrong here, but doesn't a fetch_status of 0 mean a SUCCESSFUL fetch? In other words, it SHOULD be:
if @@FETCH_STATUS = 0 --keeping doing if no errors
BEGIN -->begin of if
--do stuff blah blah
END
else
BEGIN
--the resev number doesn't exist
raiserror 77777 'Reseravtion does not exist'
return 0
END

brian perry

 

[DwayneL]

I Always think postive values are succesful, Thank you I will give that I go,