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