SQL Stored Procedures

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.

You can place an order similar to this with us. You are assured of an authentic custom paper delivered within the given deadline besides our 24/7 customer support all through.