Курсовая работа: Автоматизированная система бронирования авиабилетов

DECLARE @count int;

IF EXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number1cl>0)

SELECT @count=Aircrafts.Count1

-(SELECT SUM(Number1cl) FROM Orders WHERE FlightID=@flightID)

FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID

AND Flights.AircraftID=Aircrafts.AircraftID

ELSE

SELECT @count=Aircrafts.Count1

FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID

AND Flights.AircraftID=Aircrafts.AircraftID

RETURN @count

END'

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountEmptyPlaces2cl]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[CountEmptyPlaces2cl]

(

@flightID int

)

RETURNS int

BEGIN

DECLARE @count int;

IF EXISTS (SELECT OrderID FROM Orders WHERE FlightID=@flightID AND Number2cl>0)

SELECT @count=Aircrafts.Count2