Reputation: 888
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