MS SQL Server 2014: which stored procedure is my error in?

Question

As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once -

When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error?

Is there a particular way to handle errors from Transact-SQL that identifies the location of the error in a given stored procedure?

Can program code in a stored procedure obtain the name of the procedure it is in?

I am prepared to put "SET @MyNameIs = N'HastilyWrittenProcedure' " in program code if it's the only way. Then I can put "PRINT @MyNameIs + ' broke.' " in the error handler in everything.

Answer 1

Use @@procid to get the currently executing routine and object_name() to convert the id to a name

Hth

Answer 2

When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error?

In your CATCH block, you can use ERROR_PROCEDURE() to get the name of the procedure or trigger where the error occurred.

If you simply use THROW in your CATCH block in the inner procedures, then your outer procedure can access these system functions (ERROR_PROCEDURE(), ERROR_LINE() etc) to retrieve the information about the inner procedure failure in the outer procedure's CATCH block.

Is there a particular way to handle errors from Transact-SQL that identifies the location of the error in a given stored procedure?

In your CATCH block, you can use ERROR_LINE() to get the name of the procedure or trigger where the error occurred.

Can program code in a stored procedure obtain the name of the procedure it is in?

Use this code: SELECT OBJECT_NAME(@@PROCID) to fetch the procedure name inside the procedure itself.

Here's an example of these in use in a nested procedure scenario.

Answer 3

Another idea using Extended Events.

Create an extended events session to catch errors

CREATE EVENT SESSION [Track_Errors] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'D:\some_path\track_errors',max_file_size=(50),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO


ALTER EVENT SESSION [Track_Errors] ON SERVER STATE = START

Then in your nested stored procedures, use RAISEERROR or THROW to print out the name of the stored procedure in the message column of extended events.

RAISERROR ('sp_test_nested_error', 16, 1) WITH NOWAIT
THROW 50000, N'sp_test_nested_error', 1;

Here are the two stored procedures I used to test

-- nested stored procedure
CREATE procedure [dbo].[sp_test_nested_error]
AS

BEGIN TRY
-- Generate a divide-by-zero error  
  SELECT
    1 / 0 AS Error;
END TRY
BEGIN CATCH
    THROW 50000, N'sp_test_nested_error', 1;
END CATCH;
GO


-- parent stored procedure
CREATE procedure [dbo].[sp_test_proc]
AS

exec sp_test_nested_error
GO

And the resulting extended events output