SQL Stored Procedures

    0

    Create a stored procedure NEWGUESTBOOKING that adds a booking for a new guest.

    The stored procedure NEWGUESTBOOKING receives the following parameters

    Input Parameters
    Guest name
    Guest address
    HotelNo
    RoomNo
    dateFrom
    dateTo

    Output Parameters
    BookingNo int
    BookingResult char(40)

    The stored procedure
    1.  Set autocommit = OFF;
    2.  Issue the START TRANSACTION;
    3.  Get the max guestNo in the database.
    4.  Insert a new guest into the guest table using the max_guestno + 1, and the input parameters of the guest name.
    5.  Check if the HotelNo is valid, if not rollback and exit. Code CONTINUE HANDLE Exception for NOT FOUND and set a HOTELINVALID FLAG ON.
    6.  Check if the RoomNo  is valid, if not rollback and exit. Code CONTINUE HANDLE Exception for NOT FOUND and set a ROOMINVALID FLAG ON.
    7.  Optionally, check if a booking already exist for the hotelno, roomno for the requested stay, and if room is not available rollback and exit.  Code EXIT HANDLE Exception for this.(This is complex and will be optional).
    8.  If hotel and roomno is valid, (and optionally if the room is available) insert a booking record for the specified stay, and COMMIT.  An ELSE Condition should be coded and a ROLLBACK issued setting the output parameters as BookingNo = 0, BookingResult = ‘ERROR Hotel or Room INVALID’.
    9.  Test booking a hotel stay in your name for 2020-05-20 to 2020-05-25 at Hotel of your choice.

    Submit the CREATE PROCEDURE statement to blackboard and image of a successful test results.

                                                                                                                                      Order Now