Suppose you want to find out the name of the procedure that calls the procedure being executed,you can use system function @@PROCID which returns the object id of the current procedure.

Consider the following procedures. test1 calls the procedure test2 which actually logs some informations to the log table. What, if you want to know the calling procedure name also?. test2 accepts @@procid as input parameter and with the help of the object_name function, the name of the calling procedure is known inside the procedure test2. It is also possible to know in any number of procedures. Note that when you execute the procedure test1, it calls both test2 and test3 who know the calling procedure name.

create procedure test1
as
declare @procid int
set @procid=@@procid
exec test2 @procid
exec test3 @procid

go

create procedure test2(@procid int)
as
--Some stuff
--Log the source if any error
If @@error<>0
insert into log_table(procedure_name,column_list)
select object_name(@procid),....
GO

create procedure test3(@procid int)
as
select object_name(@procid) as calling_procedure

Result

calling_procedure
-----------------
test1