SQL 更新

USE [FeiPiao_BookHotelRoom]
GO
/****** Object:  StoredProcedure [dbo].[FP_PR_FlightOrderTicket]    Script Date: 01/24/2014 11:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
 
-- =============================================
ALTER PROCEDURE [dbo].[FP_PR_T1]
    @ofmuId INT,
    @LeaveProvinceID INT,
    @LeaveCityID INT,
    @FlightNumber VARCHAR(15),
 @FlightDate DATETIME,
 @LeaveTakeOffAirport INT,
 @LeaveArriveFlightDate DATETIME,
 @LeaveArriveAirport INT,
 @TripStatus INT,
 @LeaveFlightPrice money=0,
 @LeaveSettlementPrice money=0,
 @ReturnProvinceID INT=null,
 @ReturnCityID INT=null,
 @ReturnFlightNumber VARCHAR(50)=null,
 @ReturnFlightDate DATETIME=null,
 @ReturnTakeOffAirport INT=null,
 @ReturnArriveFlightDate DATETIME=null,
 @ReturnArriveAirport INT=null,
 @ReturnFlightPrice money=0,
 @ReturnSettlementPrice money=0,
 @Returnfnid INT=null,
 @FsId INT,
 @FlightTicketCheckOutDate DATETIME,
 @IsSynchronous int=null --是否同步
AS
BEGIN
    IF(@IsSynchronous=0) --不同步
  BEGIN
             IF(@TripStatus=0)
        BEGIN
       UPDATE dbo.FP_OrderFlightsMakeUp SET LeaveProvinceID=@LeaveProvinceID,
       LeaveCityID=@LeaveCityID,FlightNumber=@FlightNumber,FlightDate=@FlightDate,
       LeaveTakeOffAirport=@LeaveTakeOffAirport,LeaveArriveFlightDate=@LeaveArriveFlightDate,
       LeaveArriveAirport=@LeaveArriveAirport,TripStatus=@TripStatus,LeaveFlightPrice=@LeaveFlightPrice,
       LeaveSettlementPrice=@LeaveSettlementPrice,ReturnProvinceID=@ReturnProvinceID,
       ReturnCityID=@ReturnCityID,ReturnFlightNumber=@ReturnFlightNumber,
       ReturnFlightDate=@ReturnFlightDate,ReturnTakeOffAirport=@ReturnTakeOffAirport,
       ReturnArriveFlightDate=@ReturnArriveFlightDate,ReturnArriveAirport=@ReturnArriveAirport,
       ReturnFlightPrice=@ReturnFlightPrice,ReturnSettlementPrice=@ReturnSettlementPrice,Returnfnid=@Returnfnid,
       FsId=@FsId,FlightTicketCheckOutDate=@FlightTicketCheckOutDate,FlightStatus=4
       WHERE ofmuId=@ofmuId
       
      END 
     ELSE
      BEGIN
         UPDATE dbo.FP_OrderFlightsMakeUp SET LeaveProvinceID=@LeaveProvinceID,
       LeaveCityID=@LeaveCityID,FlightNumber=@FlightNumber,FlightDate=@FlightDate,
       LeaveTakeOffAirport=@LeaveTakeOffAirport,LeaveArriveFlightDate=@LeaveArriveFlightDate,
       LeaveArriveAirport=@LeaveArriveAirport,TripStatus=@TripStatus,LeaveFlightPrice=@LeaveFlightPrice,
       LeaveSettlementPrice=@LeaveSettlementPrice,
       FsId=@FsId,FlightTicketCheckOutDate=@FlightTicketCheckOutDate,FlightStatus=4
       WHERE ofmuId=@ofmuId
      END
      --修改订单预约申请为已出票
      UPDATE dbo.FP_ScenicTicketsBook SET Status=5,UpdateDate=GETDATE() WHERE OrderProcessID=
      (
      SELECT TOP 1 OrderProcessID  FROM FP_OrderFlightsMakeUp WHERE ofmuId=@ofmuId
      )
  END
    ELSE
  BEGIN
   CREATE TABLE #temp_stb
   (
     id int identity(1,1),
     ofmuId INT
   )
   declare @n        int--流水号
   declare @rows     int--行数
   set  @n=1
   DECLARE @OrderProcessID INT
   INSERT #temp_stb(ofmuId)
    SELECT ofmuId FROM FP_OrderFlightsMakeUp AS F INNER JOIN FP_OrderProcess AS OP  ON F.OrderProcessID=OP.OrderProcessID
   WHERE OP.OrderCode=(SELECT OrderCode FROM FP_OrderFlightsMakeUp AS m  INNER JOIN FP_OrderProcess AS o
   ON m.OrderProcessID=o.OrderProcessID
   WHERE ofmuId=@ofmuId)
   --设置受影响行数
   set  @rows = @@rowcount
   while @n <= @rows
    BEGIN
     SELECT @ofmuId=ofmuId FROM #temp_stb WHERE id=@n
     IF(@TripStatus=0)
        BEGIN
       UPDATE dbo.FP_OrderFlightsMakeUp SET LeaveProvinceID=@LeaveProvinceID,
       LeaveCityID=@LeaveCityID,FlightNumber=@FlightNumber,FlightDate=@FlightDate,
       LeaveTakeOffAirport=@LeaveTakeOffAirport,LeaveArriveFlightDate=@LeaveArriveFlightDate,
       LeaveArriveAirport=@LeaveArriveAirport,TripStatus=@TripStatus,LeaveFlightPrice=@LeaveFlightPrice,
       LeaveSettlementPrice=@LeaveSettlementPrice,ReturnProvinceID=@ReturnProvinceID,
       ReturnCityID=@ReturnCityID,ReturnFlightNumber=@ReturnFlightNumber,
       ReturnFlightDate=@ReturnFlightDate,ReturnTakeOffAirport=@ReturnTakeOffAirport,
       ReturnArriveFlightDate=@ReturnArriveFlightDate,ReturnArriveAirport=@ReturnArriveAirport,
       ReturnFlightPrice=@ReturnFlightPrice,ReturnSettlementPrice=@ReturnSettlementPrice,Returnfnid=@Returnfnid,
       FsId=@FsId,FlightTicketCheckOutDate=@FlightTicketCheckOutDate,FlightStatus=4
       WHERE ofmuId=@ofmuId
       
      END 
     ELSE
      BEGIN
         UPDATE dbo.FP_OrderFlightsMakeUp SET LeaveProvinceID=@LeaveProvinceID,
       LeaveCityID=@LeaveCityID,FlightNumber=@FlightNumber,FlightDate=@FlightDate,
       LeaveTakeOffAirport=@LeaveTakeOffAirport,LeaveArriveFlightDate=@LeaveArriveFlightDate,
       LeaveArriveAirport=@LeaveArriveAirport,TripStatus=@TripStatus,LeaveFlightPrice=@LeaveFlightPrice,
       LeaveSettlementPrice=@LeaveSettlementPrice,
       FsId=@FsId,FlightTicketCheckOutDate=@FlightTicketCheckOutDate,FlightStatus=4
       WHERE ofmuId=@ofmuId
      END
     
     
     --修改订单预约申请为已出票
     UPDATE dbo.FP_ScenicTicketsBook SET Status=5,UpdateDate=GETDATE() WHERE OrderProcessID=
     (
     SELECT TOP 1 OrderProcessID  FROM FP_OrderFlightsMakeUp WHERE ofmuId=@ofmuId
     )
     SET @n = @n + 1
    END
   --删除表临时表
     IF EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temp_stb') and type='U')
     DROP TABLE #temp_stb 
    END
END