We have several log tables where among other things save the name of the current stored procedure. Typically, there is a dedicated stored procedure that writes to a log table, and we pass @@procid to a parameter.
But there are cases where this is not really enough. Maybe the stored procedure that called the logging procedure itself is a general procedure. Or if we want log something by means of a trigger, we want to know the stored procedure that fired the trigger.
The purpose for this information, is mainly for tracking down problems in the application, and how some funny data may have ended up.
So what would be nedded would be a way to retrieve the entire call stack. At a minimum, we need database, schema and module (preferrably as names, rather than ids). Dynamic SQL would be represented by some placeholder.
Real nice would also be to have the line number where the next item on the stack was invoked, and for a real luxury actual parameter values would also be included.
Note that here is an important security observation: since the stored procedure are typically run by non-priv users, it must not be the case that such a function returns nothing, because the user does not have VIEW DEFINITION on the objects. This needs to be solved, for instance by relying on ownership chaining. Or from the simple fact, that if the module is on the call stack, the user indeeds has some permission to execute them.