TSQL存储过程示例之事务处理

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TAP_SP_OrderItems_UpdatePNR]
    (
    @itemId int,
    @PNR nvarchar(50),
    @SalesPrice int,
    @Company  nvarchar(50),
    @CNumber  nvarchar(50),
    @Incentive bigint,
    @Commission bigint,
    @FuelTax bigint,
    @Tax bigint,
    @TotalNet bigint,
    @TKTNumber nvarchar(50),
    @profit bigint,
    @INV# nvarchar(100),
    @ticketurl nvarchar(200)=''
    )

AS
    /* SET NOCOUNT ON */
    declare @result int
    select @result=0
    begin transaction 
        update tapOrder set profit=@profit
        where OrderID=(select OrderID from taporderItems where itemid=@itemid)
         select @result+=@@ERROR
        update taporderitems set 
         PNR = @PNR,
         salesPrice = @salesprice,
         Company=@company,
         CNumber=@CNumber,
         Incentive=@Incentive,
         Commission=@Commission,
         FuelTax=@FuelTax,
         Tax=@Tax,
         TotalNet=@TotalNet,
         TKTNumber = @TKTNumber,
         INV#=@INV#,
         ticketurl=@ticketurl         
         where itemid =@itemid
         select @result+=@@ERROR
          
    if(@result <>0)
        begin
            rollback transaction
            return 0
        end
    else
        begin
           commit transaction
           return 1
        end

在存储过程中使用事务