存储过程中使用事务的“正规”写法

在存储过程中使用事务一个重要问题是：如果事务出错了，如何回滚？ 在SQL SERVER 2005 之前 都是使用@@ERROR 来判断。

自2005 以后，可以使用try catch 方式了。

下面是使用try catch  来处理事务的例子。

• 首先建立一个tb_test1 的表，里面有一个字段 A  int， 然后增加了一个约束。不能超过4

CREATE TABLE [dbo].[TB_TEST1](
	[A] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TB_TEST1]  WITH CHECK ADD  CONSTRAINT [CK_TB_TEST1] CHECK  (([a]<(4)))
GO

ALTER TABLE [dbo].[TB_TEST1] CHECK CONSTRAINT [CK_TB_TEST1]
GO

•  下面是对这个表进行INSERT 的存储过程

CREATE PROC [dbo].[Usp_additem]
AS
  BEGIN
      SET nocount ON

      BEGIN try
          BEGIN TRANSACTION

          INSERT INTO TB_TEST1
          VALUES      (1)

          INSERT INTO TB_TEST1
          VALUES      (2)

          INSERT INTO TB_TEST1
          VALUES      (3)

          INSERT INTO TB_TEST1
          VALUES      (4)

          COMMIT TRANSACTION
      END try

      BEGIN catch
          DECLARE @errmsg VARCHAR(4000)

          SET @errmsg = 'usp_addItem 存储过程发生了错误 原因为：' + Error_message()

          IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION
            END

          RAISERROR(@errmsg,
                    16,
                    1);
      END catch
  END 

• 为什么要加入SET NOCOUNT ON
  
  • 　　这个设置影响了 @@rowcount，也就是每次执行SQL 语句之后会返回 N 行首影响。
  • 将其设置为ON ，可以优化性能。减少网络传输量。
  • 最重要的一点，C++ BUILDER 和 Delphi 调用存储过程时，如果存储过程使用了TRY CATCH, 当异常发生时， 客户端不会捕获到错误。加入了 SET NOCOUNT ON 之后，当存储过程发生异常后，客户端代码才能捕获到异常。
• 为什么要加入 RAISERROR(@ERRMSG,16,1)
  
  • 　　这段代码相当于C++C#DelhpiJAVA  中异常处理的 throw
  • 为什么，第二个参数填16， 第2个参数一般情况下填 严重性级别，过大或者过小都不行。一般填写 16。 如果太小，会因为错误不严重而被过滤掉，如果太大，会中断数据库链接。具体产看MSDN 的帮助 https://msdn.microsoft.com/ZH-CN/LIBRARY/ms164086
  • 最后一个参数写1 ，根据MSDN 应该是0~255 的整数。用于错误定义，比如 0=正常 ，1=非法参数，2=网络异常 等等。
• 这个和@@ERROR 相比好处在那里？
  
  • 可以参考《SQL SERVER 2008 入门经典》 P320 页