概述
最近因为业务的需求写了一段时间存储过程，发现之前写的存储过程存在一些不严谨的地方，特别是TRY...CATCH中嵌套事务的写法；虽然之前写的并没有错，但是还是埋藏着很大的隐患在里面。希望这篇文章能给大家一些参考；文章内容有点长还望耐心阅读。
1.插入测试数据
----创建表 DROP TABLE score GO CREATE TABLE [dbo].[score]( id INT NOT NULL PRIMARY KEY IDENTITY(1,1), name VARCHAR(50) NOT NULL, score INT NOT NULL CHECK (score>=0), months INT NOT NULL, createtime DATETIME NOT NULL DEFAULT GETDATE() ) ---根据姓名月份查询分数 CREATE INDEX IX_score_name ON score(name,months) include(score) ---根据月份查询最高分数 CREATE INDEX IX_score_months ON score(months) include(name,score) ---创建姓名和月份组合的唯一索引 CREATE UNIQUE INDEX IX_score_months_name ON score(months,name) ------插入测试数据 TRUNCATE TABLE score INSERT INTO score(name,score,months) VALUES('li',50,10),('chen',70,10),('zhang',80,10),('wang',90,10),('li',50,11),('chen',70,11),('zhang',80,11),('wang',90,11) SELECT * FROM score;
2.THROW
THROW是在2012版本中引入的，在有些场景当中，应用程序端不做一些合法性的验证，这些验证会被放在数据库端来验证。当数据库端验证输入的信息不合法时需要主动抛出异常来中断代码的执行。
THROW既可以接收错误信息抛错提示，同时也可以手动抛出错误到CATCH中。语法如下：
;THROW THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] 参数 error_number 表示异常的常量或变量。 error_number是int并且必须为大于或等于 50000 且小于或等于 2147483647，如果CATCH中使用RAISERROR来接收错误信息那么指定的error_number必须在sys.messages 中存在；如果使用CATCH来接收则不需要。 消息 描述异常的字符串或变量。 消息是nvarchar(2048)。 状态 在 0 到 255 之间的常量或变量，指示与消息关联的状态。 状态是tinyint。
注意：
1.THROW代码前必须要用分号，因为THROW会中断代码的执行，所以如果将THROW放在CATCH中时必须放在ROLLBACK TRAN之后，否则不会回滚事务导致对象一直处于提交状态被锁。
2.THROW放CATCH中可以达到RAISERROR一样的效果，同时还简便了代码。
3. THROW能返回正确的错误代码行号，而RAISERROR没办法
参考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/throw-transact-sql
3.sp_addmessage
自定义错误号
EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'Manual cast wrong ', @lang = 'us_english'; EXEC sp_addmessage @msgnum = 60000, @severity = 16, @msgtext = N'手动抛错', @lang = '简体中文';
注意：自定义错误号必须大于50000
二、调用存储过程
1.查询存储过程
----查询存储过程 CREATE PROCEDURE Pro_score (@Option VARCHAR(50), @name VARCHAR(50)='', @months INT='' ) AS BEGIN ---查询指定人分数 IF @Option='GetScore' BEGIN SELECT name, score FROM score WHERE name=@name END ----查询指定月份最高分数 IF @Option='MonthMaxScore' BEGIN SELECT Top 1 name, score FROM score WHERE months=@months ORDER BY score END END
调用存储过程：
EXEC Pro_score @Option='GetScore',@name='li' EXEC Pro_score @Option='MonthMaxScore',@months=11
3.修改存储过程
1 CREATE PROCEDURE [dbo].[Pro_Insert_score] 2 (@Option VARCHAR(50), 3 @name VARCHAR(50)='', 4 @months INT=0, 5 @score INT=0 6 ) 7 AS 8 BEGIN 9 DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000); 10 IF @Option='InsertScore' 11 BEGIN 12 13 -----使用事务 14 BEGIN TRY 15 BEGIN TRAN 16 INSERT INTO score(name,score,months) 17 VALUES(@name,@score,@months) 18 19 ----插入重复值报错事务回滚 20 INSERT INTO score(name,score,months) 21 VALUES(@name,@score,@months) 22 23 COMMIT TRAN 24 25 END TRY 26 BEGIN CATCH 27 SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); 28 RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ; 29 ROLLBACK TRAN 30 ;THROW 31 ----执行失败 32 RETURN 1 33 END CATCH 34 35 ----执行成功 36 RETURN 0 37 END 38 39 END
调用存储过程
----调用存储过程2 DECLARE @status INT EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90 SELECT @status
可以发现使用RAISERROR抛错出来的行号和消息号都是错误的，50000这个消息号其实是不存在的，它是保留的一个统一的消息号。
可以通过查询sys.message查询对应的消息号
SELECT * FROM score WHERE name='chen' SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052
4.手动抛错中断
手动抛错也是这篇文章主要要讲的一个知识点，在有一些业务场景当中有一些验证操作需要在数据库中进行，甚至必须在更新之后进行但是又是正常的提交操作，在这种情况下就需要手动进行验证是否需要执行下面的代码。，见过很多程序员写存储过程喜欢在每一个判断的地方加上RETURN操作，目的是为了不执行后面的代码，同时又在RETURN前加上ROLLBACK操作。这虽然是一个办法，但是在事务中运用RETURN是一个很危险的操作，弄不好会导致事务一直处于打开操作导致表一直被锁住，在生成环境是很危险的操作。
建议使用THROW来手动进行抛错，THROW抛错会相当于触发一个11-19级别的错误，这样会跳到CATCH中做ROLLBACK操作。
注意：THROW前必须以分号开头，如果THROW前有代码以分号结尾也可以。
CREATE PROCEDURE [dbo].[Pro_score_throw] (@Option VARCHAR(50), @name VARCHAR(50)='', @months INT=0, @score INT=0 ) AS BEGIN DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000); IF @Option='UpdateScore' BEGIN -----使用事务 BEGIN TRY BEGIN TRAN UPDATE score SET score=score+@score WHERE name=@name AND months=@months ----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去 IF (SELECT score FROM score WHERE name=@name AND months=@months)>100 BEGIN ;THROW 60000,'分数不能大于100',111 END COMMIT TRAN END TRY BEGIN CATCH
ROLLBACK TRAN ;THROW END CATCH ----执行成功 RETURN 0 END END
调用存储过程
DECLARE @status INT EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40 SELECT @status
5.存储过程调用存储过程
CREATE PROCEDURE [dbo].[Pro_score_ProcToProc] (@Option VARCHAR(50), @name VARCHAR(50)='', @months INT=0, @score INT=0 ) AS BEGIN DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000); IF @Option='Update' BEGIN ----判断修改的人是否存在 IF NOT EXISTS(SELECT * FROM score WHERE name=@name) BEGIN ---修改人不存在 RETURN 2 END ELSE BEGIN -----使用事务 BEGIN TRY BEGIN TRAN UPDATE score SET createtime='1900-01-01 00:00:000' WHERE name=@name AND months=@months SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months ---定义事务保存点 ---SAVE TRAN TRAN1 ----调用别的存储过程 EXEC Pro_score_ProcToProc @Option='UpdateScore',@name=@name,@months=@months,@score=@score COMMIT TRAN END TRY BEGIN CATCH SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months IF @@TRANCOUNT > 0 ROLLBACK TRAN ; SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months ;THROW END CATCH END ----执行成功 RETURN 0 END IF @Option='UpdateScore' BEGIN ---使用事务 BEGIN TRY BEGIN TRAN UPDATE score SET score=score+@score WHERE name=@name AND months=@months ----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去 IF (SELECT score FROM score WHERE name=@name AND months=@months)>100 BEGIN ;THROW 60000,'分数不能大于100',111 END COMMIT TRAN END TRY BEGIN CATCH ----回滚到指定保存点 ----ROLLBACK TRAN TRAN1 --回滚事务 ROLLBACK TRAN ----执行失败 ;THROW END CATCH END END
存储过程调用存储过程事务的三种处理方法:
1.内部存储过程不要包含事务，因为内部ROLLBACK会直接回滚到外部的BEGIN TRAN导致外部的ROLLBACK没有对应的COMMIT;
2.还有一种方法是在调用内部存储过程之前使用保存点“SAVE TRAN TRAN1”,同时内部存储过程的ROLLBACK TRAN必须指定事务保存点，例如“ROLLBACK TRAN TRAN1”,这样内部存储过程回滚就只会回滚到保持点.
3.在外部存储过程的CATCH块的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件
事务嵌套事务的理解
---事务1 BEGIN TRAN ---事务2 BEGIN TRAN COMMIT TRAN /ROLLBACK TRAN COMMIT TRAN /ROLLBACK TRAN
对于事务嵌套事务，事务2的ROLLBACK操作会直接回滚到事务1的BEGIN TRAN，会导致事务1的ROLLBACK没有对应的BEGIN TRAN。处理方法可以在调用事务2之前定义一个事务保存点或者在事务1的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件是否存在事务需要回滚。
SET XACT_ABORT ON
并不是所有的错误都能被CATCH所接收。对于严重级别为0-10（信息性消息）和20-25（致命的消息）是不能被CATCH所接收的，这时如果在事务中遇到了这类的报错那么通用会导致事务处理打开状态，这时就需要开启XACT_ABORT。当开启XACT_ABORT后只要代码中存在报错就会执行回滚操作，而不管错误的级别。例如：
CREATE TABLE [dbo].[AA]( [id] [int] NULL ) ON [PRIMARY] GO CREATE PROC Pro_bb (@Option VARCHAR(50)) AS BEGIN IF @OPTION='a' BEGIN TRUNCATE TABLE AA; SELECT * FROM AA; ----事务1 BEGIN TRY BEGIN TRAN INSERT INTO AA SELECT 2 SELECT * FROM AA; INSERT INTO #BB SELECT 1 COMMIT TRAN; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; ;THROW END CATCH END END
由于临时表#BB不存在，导致插入报错，但是严重级别又小于11导致CATCH接收不到错误，这时查看发现事务处于打开状态，而且表AA也被锁住。
EXEC Pro_bb @OPTION='a'; DBCC OPENTRAN;
加上事务前加上 SET XACT_ABORT ON
ALTER TABLE [dbo].[AA]( [id] [int] NULL ) ON [PRIMARY] GO CREATE PROC Pro_bb (@Option VARCHAR(50)) AS BEGIN IF @OPTION='a' BEGIN SET XACT_ABORT ON TRUNCATE TABLE AA; SELECT * FROM AA; ----事务1 BEGIN TRY BEGIN TRAN INSERT INTO AA SELECT 2 SELECT * FROM AA; INSERT INTO #BB SELECT 1 COMMIT TRAN; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; ;THROW END CATCH END END
再次执行
EXEC Pro_bb @OPTION='a'; DBCC OPENTRAN;
没有处于打开的事务而且事务也执行了回滚操作。
总结
1.建议2012以后版本所有的接收抛错改成使用THROW，不要使用THROW抛错又使用RAISERROR来介绍错误，在事务嵌套事务的写法中如果内部事务使用RAISERROR来接收THROW返回的报错不会执行后面的ROLLBACK。
2.建议在ROLLBACK前统一加上IF @@TRANCOUNT > 0判断条件，这样可以避免因为内部的ROLLBACK回滚或者RETURN操作导致ROLLBACK没有对应的COMMIT。
3.建议不要在事务内使用RETURN返回代码错误位置，RETURN会跳出事务导致提示ROLLBACK没有对应的COMMIT,严重的会导致事务一直处于打开不提交，THROW也可以指定错误位置。
4.CATCH只是用来处理TRY报错之后的逻辑，不要认为代码执行到了CATCH的ROLLBACK就会结束处理，除非是在ROLLBACK后加入了RETURN或者THROW之类的中断代码执行的命令，否则代码还将继续执行ROLLBACK之后的代码甚至END CATCH之后的代码（如果存在）。
本文转自pursuer.chen(陈敏华)博客园博客，原文链接：http://www.cnblogs.com/chenmh/p/7856777.html，如需转载请自行联系原作者
