Sqlserver2005事务回滚案例

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_UpdateDepartCode]
 @oldCode varchar(50),
    @newCode varchar(50),
    @DepartID int,
    @DepartName varchar(30),
    @DepartAddress varchar(30),
    @PersonID int,
    @CompanyID int,
    @Result int output
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
    declare @sqlError int
    set @sqlError = 0
 SET NOCOUNT ON;
    Update tblDepart set ParentDepartCode = @newCode where ParentDepartCode = @oldCode
    set @sqlError = @sqlError + @@error
    Update tblDepart set DepartCode=replace(DepartCode,substring(DepartCode,1,4),ParentDepartCode) where ParentDepartCode = @newCode
    set @sqlError = @sqlError + @@error
    Update tblDepart set DepartCode = @newCode,DepartName=@DepartName,DepartAddress=@DepartAddress,PersonID=@PersonID,CompanyID=@CompanyID where DepartID = @DepartID
    set @sqlError = @sqlError + @@error
   
    if @sqlError <> 0
    begin
      rollback transaction
      select @Result = 0
    end
    else
    begin
       BEGIN TRANSACTION
       commit transaction
       select @Result = 1
    end

   

   
END