T-SQL - Called called the current proc

Question

Is there way to tell what proc called the currently executing stored procedure.

Answer 1

There is currently no way to do this.

But please... Go here and let them know that you want that feature.

EDIT:

I looked into this a little more, and found a potential workaround (ugly to use for this purpose, but it looks like it might be doable):

SET CONTEXT_INFO

This is from Books Online:

• Session context information is stored in the context_info column in the master.dbo.sysprocesses table. This is a varbinary(128) column.
• SET CONTEXT_INFO cannot be specified in a user-defined function. You cannot supply a null value to SET CONTEXT_INFO because the sysprocesses table does not allow null values.
• SET CONTEXT_INFO does not accept expressions other than constants or variable names. To set the context information to the result of a function call, you must first place the function call result in a binary or varbinary variable.
• When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger completes.

So here's my idea:

add this code to all of your procedures:

DECLARE @UID VARBINARY(128)
SELECT @UID = CAST('ThisProcedureName' AS VARBINARY(128))
SET CONTEXT_INFO @UID

Then you add this check where you need it:

DECLARE @Message varbinary(128)
SELECT @Message = cast('TestB' as varbinary(128))

IF (@Message = CONTEXT_INFO()) BEGIN
    RAISERROR('Not Allowed unless from procedure XX', 16, 1)
END