博客

带回滚事务的存储过程 try…catch类

/***带事务的存储过程(简单模板)***/
--@@ERROR ,如果前一个Transact-SQL 语句执行没有错误，则返回 0
--Set NOCOUNT ON,防止将会话中每一条语句所影响的行数消息发回给请求的客户机
--SET XACT_ABORT ON,当 Transact-SQL 语句产生运行时错误,自动回滚当前事务

IF EXISTS(SELECT * FROM sysobjects WHERE NAME='up_dep_name' AND TYPE='p')
DROP PROCEDURE up_dep_name
GO
CREATE PROC up_dep_name @newDepName VARCHAR ( 255 ),@oldDepName VARCHAR ( 255 ) 
AS
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN --开始事务
  --更新每个表
	UPDATE LianTong_FinanceLedger SET EnumStruct =@newDepName WHERE EnumStruct = @oldDepName;	 
	UPDATE LianTong_FinanceLedgerDetails SET EnumStruct =@newDepName WHERE EnumStruct = @oldDepName;
	UPDATE LianTong_FinanceDetail SET EnumStruct =@newDepName WHERE EnumStruct = @oldDepName;
	UPDATE LianTong_ProjectContractsModel SET departmentName =@newDepName WHERE departmentName = @oldDepName;
	UPDATE LianTong_MarketContractDetial SET departmentName =@newDepName WHERE departmentName = @oldDepName;
	UPDATE LianTong_PeriodExpense SET EnumStruct =@newDepName WHERE EnumStruct = @oldDepName;
	UPDATE LianTong_ProjectContractsModel SET departmentName =@newDepName WHERE departmentName = @oldDepName;
	UPDATE LianTong_ProjectModel SET departmentName =@newDepName WHERE departmentName = @oldDepName;
	UPDATE LianTong_SalaryProject SET EnumStruct =@newDepName WHERE EnumStruct = @oldDepName;
	UPDATE LianTong_User SET DepName =@newDepName WHERE DepName = @oldDepName;
	UPDATE EPC_HistoryRecord SET ProjectName =@newDepName WHERE ProjectName = @oldDepName;
	UPDATE EPC_ProjectDetails SET ConsStructName =@newDepName WHERE ConsStructName = @oldDepName;
	-- 更新数据字典
	UPDATE EnumDictionary SET ItemName =@newDepName,ItemValue=@newDepName WHERE TableName = 'LianTong_FinanceLedger.EnumStruct' AND ItemName=@oldDepName AND ItemValue=@oldDepName;
	UPDATE EnumDictionary SET ItemName =@newDepName,ItemValue=@newDepName WHERE TableName = 'LianTong_SalaryLast.EnumStruct' AND ItemName=@oldDepName AND ItemValue=@oldDepName;
	UPDATE EnumDictionary SET ItemName =@newDepName,ItemValue=@newDepName WHERE TableName = 'LianTong_User.DepName' AND ItemName=@oldDepName AND ItemValue=@oldDepName;
	UPDATE EnumDictionary SET ItemName=@newDepName WHERE TableName = 'LianTong_FinanceDetail.EnumStruct' AND ItemName=@oldDepName;
COMMIT TRAN--事务提交
PRINT '更新成功，事务提交'
END TRY
BEGIN CATCH
	ROLLBACK TRAN --事务回滚
	PRINT '更新异常，事务回滚'
END CATCH
GO