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…