There are many situations you would like to retrieve the call stack from inside a stored procedure. The most typical case may be a logging procedure where you want to know the exact way you ended up making this log entry.
There are also situations when you want to know the caller in a module to take some action. For instance, in a trigger you may want to permit some action only if it is performed from inside a stored procedure, which you assume know what it is doing. Another example is when you break up a stored procedure in two, and you want to make sure that the sub-procedure can only be called from the main procedure. That is, in the sub-procedure you would barf if the caller is anything else.