How to get current stored procedure name dynamically

Sometimes you need to constract an error message
from a stored procedure that includes the SP`s name.

For example:
RAISERROR('The following error XXXX occured in "SP_Name"',16,1)

In these cases we do not want to hardcore the SP name in
the error string.

We can use the @@PROCID system function in this way:

DECLARE @ProcName NVARCHAR(128)
SET @ProcName =OBJECT_NAME(@@PROCID)
RAISERROR('The following error XXXX occured in "%s"',16,1,@ProcName)

The @@PROCID can be used in a stored procedure,
user-defined function, or trigger.

Reference:
http://msdn.microsoft.com/en-us/library/ms174408.aspx