Create stored Procedure Create a stored procedure which perf

Create stored Procedure

Create a stored procedure which performs the following operation on the table the_FlightDetails Based on the FlightNumber, DateofTravel, Source, Destination and TicketsRequired the procedure must provide TotalTicketCost based on the TicketsRequired and 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 available. If the required number of tickets are available it must return 0 else it must return -1 If Flight is not available for the specified date, source or destination it must return -2 Any other error, it must return -3.

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