If you have ever handled errors by using TRY-CATCH in SQL Server then you must be familiar with a function called ERROR_PROCEDURE(), which returns the stored procedure name when any error occurs during execution, and by using this function we write the procedure name with the error into our log table or event viewer for future review. But the ERROR_PROCEDURE() function does not do well with the dynamic SQL statements. If there are any dynamic SQL statements being executed inside the procedure then this function returns NULL instead of actual procedure name. This is annoying and I have no clue on this.
Interview Question: How to get the stored procedure name inside the stored procedure?
The answer is in the CATCH block.
There are two approaches and are mentioned in the catch block.
CREATE PROC usp_test_sqlindia AS BEGIN DECLARE @spName varchar (100) DECLARE @sql nvarchar (1000) BEGIN TRY SET @sql = N'SELECT 1/0 AS [SQL]' -- Devided by zero error EXEC sp_executesql @sql END TRY BEGIN CATCH SELECT @spName = OBJECT_NAME(c.objectid) FROM sys.dm_exec_sessions a INNER JOIN sys.dm_exec_connections b ON a.session_id = b.session_id CROSS APPLY sys.dm_exec_sql_text(b.most_recent_sql_handle) c WHERE a.session_id = @@spid SELECT ERROR_MESSAGE() AS [ERROR_MESSAGE], ERROR_PROCEDURE() AS [ERROR_PROCEDURE], -- This will return NULL OBJECT_NAME(@@PROCID) AS [PROCID_Approach], -- Approach 01 @spName AS [SESSION_Approach] -- Approach 02 END CATCH END GO DROP PROC usp_test_sqlindia