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
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