Say you want to return the name of the stored procedure where an error occurred… How can you do that without hardcoding the stored proc name? The answer is simple – by using the system variable @@ProcID and converting it to name.
Execute the following at the beginning of your stored proc:
DECLARE @spName nvarchar(128)
SET @spName = object_name(@@ProcID)
and you’ll have the stored proc name ready for your custom RAISERROR or any other need…