sql server：插入事务中的表以记录错误发生？

Question

我有一个 SP（存储过程），其中包含一些 T-SQL 语句......

所有 T-sql 语句都在事务块中，并且通过发生任何错误，我都会回滚所有内容。

像这样：

BEGIN TRANSACTION
.....
.....
IF @X=1
BEGIN
    declare cu cursor for select col1,col2 from Table1 where Id=@Y
    open  cu
    fetch next from cuinto @A, @B
    while  @@Fetch_Status = 0
    BEGIN
         .....
        ......
        IF @@ERROR <>0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END
END
.....
.....

Sp 无法正常运行，我找不到它的 reean 是什么...... 我认为通过将一些数据插入表中来记录 sp 中的每个操作是个好主意 我的问题是：

因为它使用事务，所以每次插入都会回滚......

您的意见是什么？还有其他办法吗？

谢谢

Answer 1

3 件事：
1）如果没有必要，请不要使用光标。
2) 您可以使用 RAISERROR WITH LOG 或 将数据插入表变量，然后将其插入到真实的表中您已回滚您的交易。这是可能的，因为表变量是独立于事务的。
3) 使用 try catch 块

Answer 2

现在没有理由使用@@ERROR：TRY/CATCH 更加可靠。要了解更多信息，我建议阅读 Erland Sommarskog 的 “SQL 2005 及更高版本中的错误处理”，它是一篇关于该主题的权威文章

在这种情况下，如果没有 TRY/CATCH，某些错误将被批量中止：这意味着代码将停止并且不会捕获任何错误。除编译错误外，此问题已通过 TRY/CATCH 修复。

该模板取自我之前的答案嵌套存储过程包含尝试捕获回滚模式？

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    -- if desired INSERT ExceptionLogTable () .. 
END CATCH
GO

如果您使用 SET XACT_ABORT ON （我认为这应该是最佳实践），那么在任何CATCH 块 @@trancount 为零。因此，如果您愿意，除了抛出错误之外，还可以在此处写入日志表。

Answer 3

我重写了您的代码，通过使用 Transaction 和 Try Catch

CREATE PROCEDURE [dbo].[mySP] 
(
    @X int, @Y int,
    @Return_Message VARCHAR(1024) = ''  OUT
)
AS

    SET NOCOUNT ON;

    Declare @A varchar(100) @B varchar(100)

BEGIN TRY

    BEGIN TRAN

        IF @X=1
        BEGIN
            declare cu cursor for select col1,col2 from Table1 where Id=@Y
            open  cu
            fetch next from cu into @A, @B
            while  @@Fetch_Status = 0
            BEGIN
                -- .....
                -- do your stuff
                FETCH NEXT FROM cu into @A, @B              
            END
        END

    COMMIT TRAN

    SELECT  @Return_Message = 'All OK'

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN 1   -- success

END TRY

BEGIN CATCH
    /*************************************
    *  if errors rollback
    *************************************/
    IF @@TRANCOUNT > 0 ROLLBACK

    SELECT @Return_Message = @ErrorStep + ' '
        + cast(ERROR_NUMBER() as varchar(20)) + ' line: '
        + cast(ERROR_LINE() as varchar(20)) + ' ' 
        + ERROR_MESSAGE() + ' > ' 
        + ERROR_PROCEDURE()

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN 0   -- fail

END CATCH

SP 用法：为您提供一个真实的示例

declare @ret int, @Return_Message VARCHAR(1024)

EXEC @ret = mySP 1, 2, @Return_Message OUTPUT

-- the SP Fail so print or store the return message with errors ...
if @ret = 0 print @Return_Message

Answer 4

您还可以使用异常处理来实现href="http://www.sqlservercentral.com/articles/news/exceptionhandlinginsqlserver2005/2237/" rel="nofollow">尝试也抓住

Begin Try
    BEGIN TRANSACTION
.....
.....
IF @X=1
BEGIN
    declare cu cursor for select col1,col2 from Table1 where Id=@Y
    open  cu
    fetch next from cuinto @A, @B
    while  @@Fetch_Status = 0
    BEGIN
        .....
        ......
        //Your insert statement....
END
.....
.....
Commit Tran

End Try

Begin Catch
      Rollback Tran
      DECLARE @ErrorMessage NVARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;

      SELECT @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

      -- Use RAISERROR inside the CATCH block to return 
      -- error information about the original error that 
      -- caused execution to jump to the CATCH block.
      RAISERROR (@ErrorMessage, -- Message text.
           @ErrorSeverity, -- Severity.
           @ErrorState -- State.
           );

End Catch