How do I tell the stored procedure name from inside the stored procedure?

1

April 24, 2013 by Kenneth Fisher

I’ve occasionally had the problem of trying to put “smart” logging messages into a process. You know the ones, you’re trying to set up logging for a process and you really want to create a somewhat generic piece of code that you can throw into each stored procedure. Something that will put a useful message into a log table. “Stored procedure xyz was run at abc time”. I don’t know about you but I hate having to hard code the name of each stored procedure, function or trigger into that type of coding. And yes I realize I still have to hard code in a message if it needs to be specific to that piece of code. This just avoids part of the work. I mean anything I can automate is to the better right?

Well during some recent reading I finally found it. @@PROCID. It returns the object id for the current transact-sql module. So for example:

 -- Create a table for my log
CREATE TABLE TestLog (
	Id INT NOT NULL IDENTITY(1,1),
	Calling_Procedure varchar(50),
	[Message] varchar(50)
)
GO

-- Create a stored procedure for my test
CREATE PROCEDURE usp_LoadTestLog AS
BEGIN
   INSERT INTO TestLog 
   VALUES 
      (
         OBJECT_NAME(@@PROCID),  -- Use OBJECT_NAME and @@PROCID to get the name of this SP
         'Test Message'
      )
END
GO

-- Run the stored procedure.
EXEC usp_LoadTestLog
GO
-- Check the table to see the results.
SELECT * FROM TestLog
GO

-- Clean up after myself
DROP TABLE TestLog
DROP PROCEDURE usp_LoadTestLog
GO 

You will see that usp_LoadTestLog gets stored in the Calling_Procedure column of TestLog.

Of course there is also ERROR_PROCEDURE but per BOL that only works “where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.”

One thought on “How do I tell the stored procedure name from inside the stored procedure?

  1. Lee Everest says:

    That is an obscure one for sure, and easily forgotten. We used to have a process at Long-Term Care where we had a table with all of the stored procedures that executed in a given batch sequence. If someone stuck some procedure in that wasn’t in the table of approved procedures, it killed the batch…we used that procedure as you have to identify the procs, and it worked well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: