SQL Server 错误处理模式

Question

我不是 SQl Server 方面的专家。这是处理 SQl SERVER 中一批 SELECT、INSERT... 中的错误的有效模式吗？ (我使用 v.2008)

BEGIN TRANSACTION
    BEGIN TRY
       -- statement 1
       -- statement 2
       -- statement 3
       COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
       ROLLBACK TRANSACTION
    END CATCH

谢谢

Answer 1

我使用这样的东西:

CREATE PROCEDURE  ErrorHandlingPattern
(     @intParam      int
     ,@varcharParam  varchar(10)
     ,@dateParam     datetime
) 
AS

BEGIN TRY
    SET NOCOUNT ON
    DECLARE @Rows              int           --store @@ROWCOUNT in this
           ,@ErrorMsg          varchar(500)  --temp string to build the contents of messages passed into RAISERROR calls
           ,@LogInfo           varchar(5000) --will hold any info necessary for error debugging, append to this throughout the procedure with important info
           ,@TransactionCount  int           

    SELECT @TransactionCount=@@TRANCOUNT
          ,@LogInfo='@intParam='     +ISNULL(''''+CONVERT(varchar(10),  @intParam       )+'''','NULL')
                 +', @varcharParam=' +ISNULL(''''+                      @varcharParam    +'''','NULL')
                 +', @dateParam='    +ISNULL(''''+CONVERT(varchar(10),  @dateParam,121  )+'''','NULL')
                 +'; @@TRANCOUNT='   +ISNULL(''''+CONVERT(varchar(10),  @@TRANCOUNT     )+'''','NULL')

    --validate parameters
    IF @intParam ....
    BEGIN --logical error
        SET @ErrorMsg='Error, invalid value for @intParam: '+ISNULL(''''+CONVERT(varchar(10),@intParam)+'''','NULL')
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    IF @TransactionCount=0  --if we are already in a transaction, no need to start another, nesting transactions +rollback=warnings about transaction count not being the same as when the procedure started.
    BEGIN
        BEGIN TRANSACTION
    END

    --do your work here....
    INSERT/UPDATE/DELETE...
    SELECT @Rows=@@ROWCOUNT

    IF @Rows!=ExpectedValue
    BEGIN --logical error
        SET @ErrorMsg='Error, INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    --append improtant info to log string
    SET @LogInfo=ISNULL(@LogInfo,'')+'; INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'

    IF @TransactionCount=0 --only end the transaction if it started here
    BEGIN
        COMMIT --put in try block to be able to catch any problems committing
    END
END TRY
BEGIN CATCH

    IF XACT_STATE()!=0 --if there is any error end the transaction ASAP
    BEGIN
        ROLLBACK TRANSACTION
    END

    --will echo back the complete original error message
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

    --because the transaction was ROLLBACKed this insert will be recorded in the database
    INSERT INTO YourErrorLog (...) VALUES (...ISNULL(@ErrorMessage,'')+ISNULL(@LogInfo,''))

    RETURN 999

END CATCH

RETURN 0
GO

由于您只是执行一批一批 SELECT、INSERT，因此您可以删除 CREATE PROCEDURE 和参数声明，并将第一行从 BEGIN TRY 开始。另外，由于您不是在创建过程，因此请将任何 RETURN 语句替换为 GOTO TheEnd 并在脚本底部添加 TheEnd: 标签。