Hotel Reservation System — Booking Procedures

Problem Description

A boutique hotel chain is building a new reservation platform on SQL Server. The platform already has its tables defined — Guest, Room, Reservation, and Reservation_Payment. They need stored procedures to handle the two core workflows: creating a new reservation (which must atomically create the reservation and charge the deposit) and recording a payment against an existing reservation.

The data model is straightforward:

• A reservation belongs to a guest and a room
• Creating a reservation requires simultaneously recording an initial deposit payment
• Payments belong to a reservation (composite key: GuestNo + ReservationNo + PaymentNo)
• A reservation can have multiple payments over time

The tech lead has stressed that the codebase must have predictable, auditable error handling that the application layer can rely on for user-facing error messages. The procedures need clear, structured failure modes — not generic exception catching. The procedures should also be composable: the payment logic should be reusable within the reservation creation workflow without duplicating code.

The following table DDL is already in place:

=============== FILE: inputs/tables.sql =============== CREATE TYPE GuestNo FROM INT NOT NULL; CREATE TYPE RoomNo FROM INT NOT NULL; CREATE TYPE ReservationNo FROM INT NOT NULL; CREATE TYPE PaymentNo FROM INT NOT NULL; CREATE TYPE _Money FROM DECIMAL(18,2) NOT NULL; CREATE TYPE _Timestamp FROM DATETIME2 NOT NULL; CREATE TYPE _Bool FROM BIT NOT NULL; CREATE TYPE _Int FROM INT NOT NULL; CREATE TYPE Name FROM NVARCHAR(100) NOT NULL; CREATE TYPE _Type FROM VARCHAR(25) NOT NULL;

CREATE TABLE ReservationStatus ( [Status] _Type PRIMARY KEY ); INSERT INTO ReservationStatus([Status]) VALUES ('Pending'), ('Confirmed'), ('CheckedIn'), ('CheckedOut'), ('Cancelled');

CREATE TABLE Guest ( GuestNo GuestNo PRIMARY KEY, FullName Name NOT NULL, Email NVARCHAR(200) NOT NULL );

CREATE TABLE Room ( RoomNo RoomNo PRIMARY KEY, RoomType _Type NOT NULL, NightlyRate _Money NOT NULL );

CREATE TABLE Reservation ( GuestNo GuestNo NOT NULL, ReservationNo ReservationNo NOT NULL, RoomNo RoomNo NOT NULL, CheckIn _Timestamp NOT NULL, CheckOut _Timestamp NOT NULL, [Status] _Type NOT NULL DEFAULT 'Pending', CreatedAt _Timestamp NOT NULL DEFAULT SYSDATETIME(),

CONSTRAINT PK_Reservation PRIMARY KEY (GuestNo, ReservationNo),
CONSTRAINT Reservation_IsMadeBy_Guest
    FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo),
CONSTRAINT Reservation_IsFor_Room
    FOREIGN KEY (RoomNo) REFERENCES Room(RoomNo),
CONSTRAINT Reservation_IsStatedBy_ReservationStatus
    FOREIGN KEY ([Status]) REFERENCES ReservationStatus([Status])

);

CREATE TABLE Reservation_Payment ( GuestNo GuestNo NOT NULL, ReservationNo ReservationNo NOT NULL, PaymentNo PaymentNo NOT NULL, Amount _Money NOT NULL, PaidAt _Timestamp NOT NULL DEFAULT SYSDATETIME(), Method _Type NOT NULL,

CONSTRAINT PK_Reservation_Payment PRIMARY KEY (GuestNo, ReservationNo, PaymentNo),
CONSTRAINT Payment_IsAppliedTo_Reservation
    FOREIGN KEY (GuestNo, ReservationNo)
    REFERENCES Reservation(GuestNo, ReservationNo)

);

Output Specification

Produce a SQL script file named procedures.sql containing:

1. A procedure to add a payment to an existing reservation — designed to work within a caller's transaction
2. A procedure to create a new reservation and simultaneously record the initial deposit — this procedure owns its transaction and calls the payment procedure internally

The procedures should handle failures cleanly so the application can present meaningful messages to guests when something goes wrong (e.g., room not found, duplicate booking, bad data).