sql-servert-sqlstored-procedures

SQL Procedure for booking flights


I'm new to SQL and I have an assignment where I need to create a procedure that should get the customer ID and calendar ID and insert the booking to the Booking table IF the seat limit has not been reached. I'm aware this is very basic but I hope it's OK anyway. I've tried googling all day and when I think I've found something it just doesn't work for me because I can't apply it right.

The tables look like this:

Customer 
(
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    Firstname       VARCHAR(15) NOT NULL,
    Lastname        VARCHAR(20) NOT NULL,
    Email           VARCHAR(50),
    Phonenumber     CHAR(12),
)

Calendar 
(
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    Departure       SMALLDATETIME NOT NULL,
    Arrival         SMALLDATETIME NOT NULL,
    Departing_from  VARCHAR(10) NOT NULL,
    Arriving_to     VARCHAR(10) NOT NULL,
    Seats           INT NOT NULL
)

Booking  
(
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    Booking_date    SMALLDATETIME NOT NULL,
    Cu_ID           INT FOREIGN KEY REFERENCES Customer(ID),
    Ca_ID           INT FOREIGN KEY REFERENCES Calendar(ID)
)

I tried this procedure but I get syntax errors

CREATE PROCEDURE booking_proc1
AS
    DECLARE @Booked_Seats INT
    DECLARE @smalldatetime smalldatetime

    SET @Booked_Seats = 0
BEGIN
    INSERT INTO Booking
        SELECT
            CASE
                WHEN @Booked_Seats < 5 
                    THEN (@smalldatetime, ID FROM Customer 
                          WHERE ID = 1, 
                          ID FROM Calendar WHERE ID = 1)
                ELSE 'Flight is full'
            END;

exec booking_proc1

Solution

  • Try this:

    /*
    Example:
    
    Seats Limit = 20
    Bookin Date = '06/15/2021'
    CustomerID  = 1
    CalendarID  = 55
    
    Call it like this:
    
    EXEC dbo.usp_Booking_Insert 20 '06/15/2021', 1, 55
    
    */
    
    
        CREATE PROCEDURE dbo.usp_Booking_Insert
                @SeatsLimit     int 
            ,   @BookingDate    datetime
            ,   @CustomerID     int
            ,   @CalendarID     int
        
            AS
                BEGIN
                    SET NOCOUNT ON;
        
                    DECLARE @SeatsBooked int = (SELECT SUM(Seats) FROM @Calendar WHERE ID = @CalendarID)
        
                    IF  @SeatsLimit > @SeatsBooked
                        BEGIN
                            INSERT INTO @Booking    VALUES (@BookingDate, @CustomerID, @CalendarID);
                        END
                    ;
                END
        GO