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.”

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, object functions, object id, sql statements, system functions, T-SQL