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