Create a stored procedure which performs the following oper

$$

Create a stored procedure which performs the following operation on the table tbl_FlightDetails

Create a stored procedure which performs the following opera Based on the FlightNumber, DateofTravel, Source, Destination . TotalTicketCost based on the TicketsRequired and tion on the table tbl FlightDetails and TicketsRequired the procedure must provide TicketCost. If the flight is available for the given source and destination for the specified date Update the TicketsAvailable column of the table based on the TicketsRequired if the required tickets are availa- ble. If the required number of tickets are available it must re- turn 0 else it must retun -1.

Solution

Go
CREATE PROC usp_GetTicketCost
(
@FlightNumber NUMERIC(6),
@DateofTravel DATE,
@Source VARCHAR(50),
@Destination VARCHAR(50),
@TicketsRequired INT,
@TotalTicketCost Decimal(8,2) OUT
)
AS
BEGIN

DECLARE @TicketsAvailable INT
DECLARE @TicketCost DECIMAL(8,2)
DECLARE @Status INT

BEGIN TRY

IF EXISTS(SELECT * FROM tbl_FlightDetails WHERE
FlightNumber = @FlightNumber and
DateofTravel = @DateofTravel and
Source = @Source and
Destination = @Destination)
BEGIN
  
SELECT @TicketsAvailable = TicketsAvailable,
@TicketCost = TicketCost
FROM tbl_FlightDetails

WHERE FlightNumber = @FlightNumber and
DateofTravel = @DateofTravel and
Source = @Source and
Destination = @Destination
  
IF(@TicketsAvailable >= @TicketsRequired)
BEGIN
UPDATE tbl_FlightDetails SET
TicketsAvailable = TicketsAvailable-@TicketsRequired
WHERE FlightNumber = @FlightNumber
SET @TotalTicketCost = @TicketCost*@TicketsRequired

PRINT \'Success\'
SET @STATUS = 0
END
ELSE
PRINT \'Tickets Available lessthan the Tickets Required\'
SET @STATUS = -1
END
ELSE
BEGIN
PRINT \'The Flight Does not exist\'
SET @STATUS = -2
END
END TRY
BEGIN CATCH
SET @STATUS = -3
PRINT \'ERROR OCCURRED\'
END CATCH
END