How to know calling stored proc name

Question

How to know calling stored proc name

shahmm

Ten Centuries

Points: 1183
More actions
December 8, 2003 at 2:23 pm

#59588

Gurus,
My problem is I am trying to find out name of stored procedure which updates a given table. The problem is there are several stored proc called by VB application updates the same table/Column and I want to capture the name of sp and not the generic name of program/username via trigger.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Take a look at the Meta Data functions.

@@PROCID returns the ID of the current procedure, OBJECT_NAME() returns the name of the database object. Used together OBJECT_NAME(@@PROCID) gives you the name of the currently executing stored procedure.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

noeld SSC Guru Points: 96590 More actions · Answer 2

From My suggestion you will have to modify a little the stored procedures as:

ALTER PROCEDURE Proc1 (@var1 ....

DECLARE @TrackValue varbinary(128)

SET @TrackValue = CAST( @@PROCID as binary(4) )

SET CONTEXT_INFO @TrackValue

-- Your Proc Code

...

INSERT

...

SET CONTEXT_INFO 0x0 --Clear at the end of the Proc

Then you can Write the trigger as:

CREATE TRIGGER AuditTrigger

ON TableName

AFTER INSERT

AS

BEGIN

DECLARE @ProcName varchar(128)

SELECT @ProcName = OBJECT_NAME( SUBSTRING

( p.context_info, 1, 4 ) )

FROM master..sysprocesses as p

WHERE p.spid = @@SPID

PRINT @ProcName

-- Or Insert On Audit Table

-- Ps The Value on the parameters are on the inserted table

END

HTH

* Noel

shahmm Ten Centuries Points: 1183 More actions · Answer 3

Thanks, all of you for your input, but I found solution yesterday itself my self, to share what i did is in the trigger I used dbcc inputbuffer(@@spid) using dynamic sql, since you can not do insert into table with dbcc... so created a table and brought the output in that table from trigger and it worked.