如何编写一个 t-sql 主程序来捕获子程序引发的错误 [英] how to write a t-sql master procedure to catch sub procedures raised error

问题描述

我是 T-SQL 编程的新手.我需要编写一个包含 5 个不同子过程的主过程.我如何构建程序，以便子程序不会中止.相反，它们将捕获错误并将它们报告回输出参数中的主程序.如果可以，请给我提供伪代码.谢谢.

I am new to T-SQL programming. I need to write a main procedures with 5 different sub procedures in it. How could i structure the program so that subprograms will not abort. Instead, they will capture errors and report them back to the main program in the output parameters. Please provide me with pseudo code if you can. Thanks.

推荐答案

类似的东西 BEGIN TRANSACTION 表示连接引用的数据在逻辑上和物理上一致的点.

something like that BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent.

COMMIT TRANSACTION 使自事务开始以来执行的所有数据修改成为数据库的永久部分，释放事务持有的资源，并将 @@TRANCOUNT 递减为 0.如果 @@TRANCOUNT 大于 1，COMMIT TRANSACTION 将 @@TRANCOUNT 减 1 并且事务保持活动状态.

COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

ROLLBACK TRANSACTION 删除从事务开始或到保存点所做的所有数据修改.它还释放事务持有的资源.

ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

ERROR_NUMBER() 返回错误编号.

ERROR_NUMBER() returns the number of the error.

ERROR_SEVERITY() 返回严重性.

ERROR_SEVERITY() returns the severity.

ERROR_STATE() 返回错误状态编号.

ERROR_STATE() returns the error state number.

ERROR_PROCEDURE() 返回发生错误的存储过程或触发器的名称.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

ERROR_LINE() 返回导致错误的例程中的行号.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_MESSAGE() 返回错误消息的完整文本.文本包括为任何可替换参数提供的值，例如长度、对象名称或时间.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

-- try block will start
        BEGIN TRY

          BEGIN TRANSACTION TranName
            EXECUTE usp_First;
        -- if sub procedure have some parameter then we can pass 
            EXECUTE usp_Second param1, param2;
            .
            .
            EXECUTE usp_Fifth;
         COMMIT TRANSACTION TranName

        END TRY
        -- if soemthing goes wrong then catch
        BEGIN CATCH 
          IF (@@TRANCOUNT > 0)
           BEGIN
              ROLLBACK TRANSACTION TranName
           END 
           -- get error detail
            SELECT
                ERROR_NUMBER() AS ErrorNumber,
                ERROR_SEVERITY() AS ErrorSeverity,
                ERROR_STATE() AS ErrorState,
                ERROR_PROCEDURE() AS ErrorProcedure,
                ERROR_LINE() AS ErrorLine,
                ERROR_MESSAGE() AS ErrorMessage
        END CATCH

有关详细信息 https://msdn.microsoft.com/en-IN/library/ms175976.aspx

这篇关于如何编写一个 t-sql 主程序来捕获子程序引发的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！