RaiseError not returning to caller as expected

Question

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

Why are you combining `try`/`catch` with `raiserror`? You should be using `throw`. — Dale K, May 03 '22 at 19:48
You need to read [this](https://www.sommarskog.se/error_handling/Part1.html) — Dale K, May 03 '22 at 19:50
Gotta disagree there @Dale K. Funny you are mentioning Erland's article, as in part two a large section is devoted to its many drawbacks. Let MS fix it first, and us use it later. — George Menoutis, May 03 '22 at 20:48
@GeorgeMenoutis not sure what you are saying you disagree with? — Dale K, May 03 '22 at 20:50
The docs are quite clear that throw should be used, and if used correctly its works as required. — Dale K, May 03 '22 at 20:54
After reading this ( https://www.sommarskog.se/error_handling/Part2.html#THROW ), you insist of choosing `throw` because "ms says so"? — George Menoutis, May 03 '22 at 20:58
No, because I have used it extensively and found it is more consistent and does what I need better than raiserror. Regardless if OP reads article they will be able to resolve their issue. — Dale K, May 03 '22 at 21:00
Boy am I humbled. Throw works. Possible duplicate of https://stackoverflow.com/questions/76346/why-does-sql-server-keep-executing-after-raiserror-when-xact-abort-is-on — George Menoutis, May 03 '22 at 21:47
@GeorgeMenoutis `THROW` is far more consistent than `RAISERROR` re exception handling, try/catch and `XACT_ABORT`, as well as in triggers. The *only* argument Erland makes against it is that it's confusing *if* you don't always terminate with a `;` — Charlieface, May 03 '22 at 21:51
You should use `THROW;` and no arguments to rethrow the original error. Do not use `THROW ...` in a catch block. Also use `SET XACT_ABORT ON;` always — Charlieface, May 03 '22 at 21:54

RaiseError not returning to caller as expected

0 Answers0