user3020047
user3020047

Reputation: 888

RaiseError not returning to caller as expected

I am using SQL Server 2106.

I have a test stored procedure with a just TRY CATCH that raises an error within it using RAISERROR.

It goes to the CATCH where I attempt to log the error with a call to a logging stored procedure before returning to the client caller. It too is encased in a TRY CATCH. I purposely incorrectly named an input argument to the logging stored procedure so as to male it fail.

As expected the call to the logging store procedure fails and the code drops into the CATCH. There I do another RAISERROR expecting to then return immediately to the caller.

However, it does not and drops to the next command which is another RAISERROR. This one does a RAISERROR to send back to the caller the initial error (when the logging does not fail).

Why is it it dropping threw AFTER the first logging CATCH error was caught? It should return to the caller at this point.

Stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[TestRaiseErrorModified]

AS 
BEGIN

    DECLARE @CurrentDateTime           DATETIME,
            @Message                   VARCHAR(MAX) = ''

    SELECT @CurrentDateTime = GETDATE()

    -- For the CATCH.
    DECLARE @ErrorLine              INT;
    DECLARE @ErrorMessage           VARCHAR(2048);
    DECLARE @ErrorNumber            INT;    
    DECLARE @ErrorSeverity          INT;    
    DECLARE @ErrorState             INT;    
    DECLARE @DatabaseName           VARCHAR(255);
    DECLARE @ServerName             VARCHAR(255);
    DECLARE @ErrorDescription       VARCHAR(MAX);
    DECLARE @CRLF                   VARCHAR(2);
    
    BEGIN TRY
       SET NOCOUNT ON;

       SELECT @Message = 'Critical Error - procedure TestRaiseErrorModified during the 
       select.'
       RAISERROR (@Message, 16, 1)
    
       -- Returns success.
       RETURN 0 
    END TRY

    BEGIN CATCH
        -- Gather these first.
        SELECT 
            @ErrorLine = ERROR_LINE()
            -- ERROR_MESSAGE() contains the RAISERROR message raised above.
            , @ErrorMessage = ERROR_MESSAGE()
            , @ErrorNumber = ERROR_NUMBER()
            , @ErrorSeverity = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE()
            , @DatabaseName = CAST(DB_NAME() AS VARCHAR)
            , @ServerName = CAST(SERVERPROPERTY ( 'ServerName' ) AS VARCHAR)
            , @CRLF  = CHAR(13) + CHAR(10)
  
        -- Build for a critical error type message.     

        SET @ErrorDescription = 'From stored procedure: '  + ERROR_PROCEDURE() 
                                        + '. Error Line: '  + CAST(@ErrorLine AS VARCHAR)
                                        + '. Error Message: ' + @ErrorMessage
                                        + ' Error Number: ' + CAST(@ErrorNumber AS VARCHAR)
                                        + '. Error Severity: ' + CAST(@ErrorSeverity AS
                                             VARCHAR)
                                        + '. Error State: ' + CAST(@ErrorState AS VARCHAR)
                                        + '. Database Name: '  + @DatabaseName
                                        + '. Server Name: ' + @ServerName

        IF (XACT_STATE() <> 0)
            BEGIN
               ROLLBACK TRANSACTION 
            END
 
        IF (@ErrorSeverity = 16) AND (@ErrorState = 2)
            BEGIN 
               -- Return the validation error to display to the User.
               RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
            END      
        ELSE 
            BEGIN 
               -- Log the critical error.   
               BEGIN TRY
                  EXEC dbo.InsertBlogErrorLog
                         @a_LogDateTime = @CurrentDateTime,    
                         @a_UserName = 'Admin',
                         @a_UserIpAddress = '32.211.50.62', 
                         @a_ObjectID = @@PROCID,
                         @a_MessagexxxType = 'S/P Critical Error',
                         @a_LogMessage = @ErrorDescription           
               END TRY
           
               BEGIN CATCH
                  -- Stack the messages. Return the logging error to display to the User.
                  SELECT @Message = 'Critical Error - procedure InsertBlogErrorLog failed. A 
                  log entry CANNOT be made. Do not continue. Contact the Admin. Initial error 
                  message: ' + @ErrorMessage
                  RAISERROR(@Message, 16, 1) 
               END CATCH

               -- Why is it it dropping threw AFTER the first logging CATCH error was caught? 
               -- It should return to the caller allready.

               -- So now it will cause 2 messages to be returned. When only 1 should be
               -- returned.

               -- Return the original error as the logging was successful.
               -- This message will be passed back to display to the User.
               SELECT @Message = 'Critical Error - do not continue. Contact the Admin and 
               provide this log date: ' + Convert(VARCHAR, @CurrentDateTime,21)
               RAISERROR(@Message, 16, 1)                             
            END

         -- Returns failure.
         RETURN 1     
    END CATCH
END

Upvotes: 0

Views: 525

Answers (0)

Related Questions