Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...