SQL Server – THROW Not Including Name of Calling Procedure Unless Custom Error Message is Specified

sql serversql-server-2012t-sql

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?

Best Answer

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.

Related Question