sql - 当对象不存在时处理事务错误

Question

我在 MSDN Lbrary 中发现了一篇文章，解释了当找不到对象时，try/catch 不会处理抛出的错误。所以，即使我在 try/catch 中包装了一个事务，回滚短语也不会执行:

BEGIN TRY
BEGIN TRANSACTION

    SELECT 1 FROM dbo.TableDoesNotExists
    PRINT ' Should not see this'
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    SELECT
            ERROR_MESSAGE()
END CATCH

--PRINT 'Error Number before go: ' + CAST(@@Error AS VARCHAR)

go
PRINT 'Error Count After go: ' + CAST(@@Error AS VARCHAR)
PRINT 'Transaction Count ' + CAST(@@TRANCOUNT AS VARCHAR)

处理对象不存在时抛出的错误的推荐方法是什么，尤其是当涉及事务时。我应该用这段代码代替最后两个打印语句吗:

IF @@ERROR <> 0 AND @@TRANCOUNT > 0
BEGIN   
    PRINT 'Rolling back txn'
    ROLLBACK TRANSACTION
END 

go

PRINT 'Transaction Count again: ' + CAST(@@TRANCOUNT AS VARCHAR)

Answer 1

您可以使用 OBJECT_ID() 测试对象是否存在:

IF OBJECT_ID('MyTable') IS NULL RAISERROR('Could not find MyTable.', 18, 0)