我们正在从DB2切换到SQL Server 2008R2的过程中,我对TSQL不太熟悉。希望有人可以帮助我更好地理解正在发生的事情。我们创建了一个名为RethrowError的存储过程:
CREATE PROCEDURE RethrowError
AS
BEGIN
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
PRINT 'yo error';
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
PRINT 'yo doin something';
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
PRINT 'yo end';
RETURN;
END
GO
我们创建这个过程的原因纯粹是为了将来不必接触所有的过程就能扩展错误。我添加了一些打印行以进行调试。
我的主要问题是,我们有一个过程A,在失败时执行RethrowError,并且我会看到消息。
yo error
yo doin something
yo end
如预期所料。
CREATE PROCEDURE dbo.A
AS
BEGIN
SET NOCOUNT ON;
DECLARE & SET VARIABLES;
BEGIN TRY
BEGIN TRANSACTION MaintainTarget
DO SOME STUFF
END TRY
BEGIN CATCH
EXEC RethrowError;
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
RETURN -101;
END CATCH;
RETURN;
END
GO
然而,我们已经创建了一个执行多个过程的程序,当嵌套的过程(即由过程B调用的过程A)失败时,我只看到以下消息
yo error
yo doin something
我不太明白为什么最后一条消息不再显示。
步骤B与步骤A类似,但在捕获方面略有不同。
CREATE PROCEDURE dbo.B
AS
BEGIN
SET NOCOUNT ON;
DECLARE & SET VARIABLES;
BEGIN TRY
DO SOME STUFF
END TRY
BEGIN CATCH
COMMIT;
RETURN -101;
END CATCH;
RETURN;
END
任何有助于更好地理解正在发生的事情的帮助都将不胜感激。