SQL SERVER 存储过程相关记录_sql server 存储过程历史记录-优快云博客

本文链接：https://blog.youkuaiyun.com/dh_2017/article/details/116205418

这个博客展示了如何使用SQL创建一个带有事务处理和异常捕获机制的存储过程。在尝试更新多个表的数据时，如果发生错误，存储过程会自动回滚事务，确保数据一致性。涉及的表包括LianTong系列和EnumDictionary，同时更新了数据字典中的相关条目。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

带回滚事务的存储过程 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