Retrieve name of running stored procedure

Hello everybody,

I would like to know if there is a function to retrieve the name of the running stored procedure, like the built-in function "DB_NAME()" to retrieve the name of the database.

I need to retrieve the name for logging possibilities.

Thank you in advance!

Bastiaan Molsbeck.

Bastiaan:

There is no direct way to do this; however, if what you have in mind is to be able to get the name of an executing stored procedure in a trigger so that you can retain this information you can (1) use SET CONTEXT_INFO in the stored procedure to store the name of the calling proc and then (2) use the CONTEXT_INFO() function from the trigger to retrieve this information. Keep in mind that CONTEXT_INFO is BINARY(128) so you will need to convert the name to binary to execute SET CONTEXT_INFO and will need to translate from BINARY back to VARCHAR when using the CONTEXT_INFO() function. Take the time to look up these two topics in books online. Here are a couple of previous threads in which there was a similar discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=485119&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1234752&SiteID=1

You will need to include something like this in the procedure:

declare @.contextBinary varbinary (128)
set @.contextBinary = ( select convert(varbinary(128), 'MyProcedureName') )

set context_info @.contextBinary

and something like this in the trigger:

declare @.callingProcedure varchar(128)

set @.callingProcedure = ( select convert(varchar(128), context_info()) )

|||

Hm, I was trying to find a way to accomplish that WITHOUT having to write the name of the stored procedure in it.

But thanks anyway. :-)

|||

If youi are talking about retrieving it from within the executing stored procedure, use object_name(@.@.proc_id) as in:

create procedure test

as

select object_name(@.@.procid)

go

exec test

This will return:

test

|||

Yes, that's exactly what I need.

Many thanks!