请教有经验的人士，所有SP都有必要用Try Catch 和 TRANSACTION 吗？

Question

如果只是查询语句没有必要用。如果有UPDATE或者DELETE语句 而且不止一个批的话 就需要用这个比较好。

Answer 1

这个写法，在原来的公司，我也建议公司的主管，采用这种写法。因为通过监控发现，经常有日结存储过程执行失败的，但是只执行了一部分。其实这样做，主要在于，有时候，在一个存储过程中，如果是这样：update insert into如果update报错了，insert into 照样能执行，所以最好是包含在一个事务中，另外，try -catch也能捕获异常，然后主动回滚。

Answer 2

这个我原来是通过应用程序来监控的，通过java来调用job，来执行存储过程的，所以能监控。

Answer 3

非常有必要，这和编程是一样的，如果一段代码没有包含在try catch里，它的风险就是不可控的。很简单的查询可以省略，是因为它基本没有风险，不需要控制。如果你的存储过程里有业务操作，如insert,update,delete，那就一定要把异常处理做好，事前多一分力，事后省十分力

Answer 4

我觉得没有必要。异常处理和记录在程序端做更好一些，数据库层面不能捕获到所有异常，捕获到异常rollback以后其实还要throw 出来，而且这种写法太冗余了。
保证事物完整性这么写就够了
SET XACT_ABORT ON
BEGIN TRANSACTION          
    .....
COMMIT TRANSACTION   
如果写批量数据处理的Job的话比较有用

Answer 5

错误处理不应该近现在某个部分，从程序端到sql端都应该做，就比如程序端也有死锁，sql端也有死锁一样，你只处理一部分的话，有问题还不知道哪里找

Answer 6

给个我常用的模板，供参考，大家也提下意见，其实这个也是从微软的示例库里抄来的，。
模板：-- =============================================
-- Author:
-- Create date: 2013-01-01
-- Description:
-- Note:
-- =============================================
CREATE PROCEDURE dbo.ProcTemplate
AS 
    BEGIN
        SET NOCOUNT ON;
        BEGIN TRY
            BEGIN TRANSACTION;
            
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 
                BEGIN
                    ROLLBACK TRANSACTION;
                END
            EXECUTE dbo.ProcLogError;
            RETURN -1;
        END CATCH;        RETURN 0;
    END;
GO
当中调用的ProcLogError如下：-- =============================================
-- Author:
-- Create date: 2013-03-20
-- Description: 用于将错误记录到ErrorLog表。
-- =============================================
CREATE PROCEDURE dbo.ProcLogError
    @ErrorLogID INT = 0 OUTPUT
AS 
    BEGIN
        SET NOCOUNT ON;
        SET @ErrorLogID = 0;
        BEGIN TRY
            IF ERROR_NUMBER() IS NULL 
                RETURN 0;
            IF XACT_STATE() = -1 
                BEGIN
                    PRINT '当前事务处于不可提交的的状态时，无法记录错误。'
                        + '请在执行ProcLogError过程前回滚事务，以便正常记录错误日志。';                    
                    RETURN 0;
                END
            INSERT  dbo.ErrorLog
                    ( UserName ,
                      ErrorNumber ,
                      ErrorSeverity ,
                      ErrorState ,
                      ErrorProcedure ,
                      ErrorLine ,
                      ErrorMessage
                    )
            VALUES  ( CONVERT(SYSNAME, CURRENT_USER) ,
                      ERROR_NUMBER() ,
                      ERROR_SEVERITY() ,
                      ERROR_STATE() ,
                      ERROR_PROCEDURE() ,
                      ERROR_LINE() ,
                      ERROR_MESSAGE()
                    );
            SET @ErrorLogID = @@IDENTITY;
        END TRY
        BEGIN CATCH
            PRINT 'ProcLogError过程在执行中发生错误: ';
            EXECUTE dbo.ProcPrintError;
            RETURN -1;
        END CATCH
    END;GO
其中使用的dbo.ProcPrintError如下：-- =============================================
-- Author:
-- Create date: 2013-03-20
-- Description: 用于输出错误信息至消息框。 
-- =============================================
CREATE PROCEDURE dbo.ProcPrintError
AS 
    BEGIN
        SET NOCOUNT ON;
        PRINT '错误 ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) + ', 严重性 '
            + CONVERT(VARCHAR(5), ERROR_SEVERITY()) + ', 状态 '
            + CONVERT(VARCHAR(5), ERROR_STATE()) + ', 过程 '
            + ISNULL(ERROR_PROCEDURE(), '-') + ', 行号 '
            + CONVERT(VARCHAR(5), ERROR_LINE());
        PRINT ERROR_MESSAGE();
    END;
GO上面使用到的ErrorLog表结构如下：-- =============================================
-- Author:
-- Create date: 2013-03-20
-- Description: 错误日志记录表。
-- =============================================
CREATE TABLE dbo.ErrorLog
    (
      ErrorLogID INT IDENTITY(1, 1) NOT NULL ,
      ErrorTime DATETIME NOT NULL ,
      UserName SYSNAME NOT NULL ,
      ErrorNumber INT NOT NULL ,
      ErrorSeverity INT NULL ,
      ErrorState INT NULL ,
      ErrorProcedure NVARCHAR(126) NULL ,
      ErrorLine INT NULL ,
      ErrorMessage NVARCHAR(4000) NOT NULL ,
      CONSTRAINT PK_ErrorLog_ErrorLogID PRIMARY KEY CLUSTERED
        ( [ErrorLogID] ASC )
    );
GOALTER TABLE dbo.ErrorLog ADD  CONSTRAINT DF_ErrorLog_ErrorTime  DEFAULT (GETDATE()) FOR ErrorTime;
GO