CODEWITHSUNDEEP
t-sqlsql-server-2008
Jonathan Allen
Jonathan Allen

Reputation: 70307

T-SQL - Called called the current proc

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

Upvotes: 1

Views: 153

Answers (2)

Andrew
Andrew

Reputation: 139

This question was in the result list when I was researching the same issue, however I found the following code

print object_name(@@PROCID)

which works a treat within a stored proc, to return the name of the stored proc which contained it. The original article I found at sql server central.

Upvotes: 0

Gabriel McAdams
Gabriel McAdams

Reputation: 58261

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

Upvotes: 1

Related Questions