7

I am experiencing some behavior with THROW that I can't understand. Consider the following stored procedure:

CREATE PROCEDURE usp_division_err AS 
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0')
END TRY
BEGIN CATCH
    THROW;
END CATCH 

When the procedure is executed, the following error is raised:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Note, no information about in which procedure the error was raised is included. That's because the erroneous, dynamic SQL is executed in another scope, and that is fine. However, change the CATCH-block to look like this

BEGIN CATCH
    THROW 50000, 'An error occurred.', 1;
END CATCH 

and the execution of the procedure will raise this error instead:

Msg 50000, Level 16, State 1, Procedure usp_division_err, Line 7 [Batch Start Line 0]
An error occurred.

The error is still encountered while executing the dynamic SQL, but when I manually specify the error number and error message (the first and second parameter of THROW), the procedure-name of the executing procedure somehow appears.

Why does the procedure-name appear in the second error-message but not the first?

  • Don't know if this helps, but change to just select 1/0 instead of Exec ('select 1/0'), and you get both: Msg 8134, Level 16, State 1, Procedure usp_division_err, Line 4 [Batch Start Line 10] Divide by zero error encountered. – Kevin3NF Nov 23 '16 at 16:01
  • 2
    Well, the only explanation is that when u use the throw without anything, you're redirecting the original exception (that you explained correctly, because of the dynamic sql, doesn't show any details). When you use the throw with custom error, you're throwing a NEW error, hence it shows you now the details. – Renato Afonso Nov 23 '16 at 16:01
  • I've found that a lot EF setups also will not return the SP name when passing the error down to a stack trace (not sure what the cause is..), so I always use FORMATMESSAGE in my outermost throw to append the SP name to the message with OBJECT_NAME(@@PROCID). – LowlyDBA Nov 23 '16 at 16:13
2

Code the CATCH like this:

BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH 

That allows you to pass all the parameters into the THROW statement so it can send them back to the caller.

I tested that like this:

USE tempdb;
IF OBJECT_ID('dbo.usp_division_err', 'P') IS NOT NULL 
DROP PROCEDURE dbo.usp_division_err;
GO
CREATE PROCEDURE dbo.usp_division_err AS 
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0');
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @errno int = 50000 + ERROR_NUMBER();
    DECLARE @state int = ERROR_STATE();
    THROW @errno, @msg, @state;
END CATCH 
GO
EXEC dbo.usp_division_err;

The results:

Msg 58134, Level 16, State 1, Procedure dbo.usp_division_err, Line 10 [Batch Start Line 16]
Divide by zero error encountered.

To obtain the native error number you just need to subtract 50000 from the reported error number. Since the parameters for THROW are optional, there must be two code-paths inside THROW, one that reports the procedure name, and one that doesn't. I'll leave it to the reader to decide which code-path does that.

  • 1
    So in the end, we need to manually specify the THROW parameters in order to get the context (the procedure) while also retaining the innermost error-message, number and state from the relevant functions. Nifty! – krystah Jul 6 '17 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.