Q1 Create the stored procedure and execution also needed Cr

$$

Q1)

Create the stored procedure and execution also needed.

Create a table ‘tbl_FlightDetails’ used to store the details of various flights

tbl_FlightDetails consists of

FlightNumber with datatype Numeric(6)

DateofTravel with datatype Date

Source with datatype varchar(50)

Destination with datatype Varchar(50)

TicketCost with datatype Decimal(8,2)

TicketsAvailable with datatype Integer

And insert the values in the tbl_FlightDetails table.

And then create the stored procedure which performs the following operations on the table tbl_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

-------- Execution for stored Procedure --------
Go

DECLARE @TotalTicketCost AS Decimal(8,2)
EXEC usp_GetTicketCost 18002,\'28-Dec-2014\',\'Bengaluru\',\'NewYork\',0,
@TotalTicketCost OUT