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