Can a MS SQL stored procedure look up it's own name?

Question

Is there any way within a stored procedure for it to reference it's own name? Say I want it to print it's own name, but not to physically type the name in the stored procedure. Is there any cheater way to get the name or id from within the procedure itself without using the actual name to find the information?

Answer 1

Try:

SELECT OBJECT_NAME(@@PROCID)

@@PROCID returns the object ID of the current SQL module. OBJECT_NAME translates an object ID to its name. Both are available from at least version 7 to 2008 of SQL Server.

Answer 2

You're looking for @@PROCID and OBJECT_NAME i.e.

select @procName=OBJECT_NAME(@@PROCID)

Answer 3

Name of proc/func

Select OBJECT_NAME(@@PROCID);

Other interesting stuff

 Select * From sys.sysprocesses Where spid = @@SPID

Answer 4

Here's some tinker code I wrote, running against a SQL Server 2005 database to play with the OBJECT_NAME(@@PROCID) function.

Run this section first, to create a log table

CREATE TABLE Diagnostics_Log 
  (
   sprocName sysname,
   WhenRun  datetime,
   Comment  Varchar(160) NULL
  )

--========================================

Then run this T-SQL to create a few versions of the same sproc, adding a numeric suffix each time.

CREATE PROC dg_test_name_logging
    @Comment Varchar(160)
AS 
  declare @Sprocname sysname
  select @sprocName = OBJECT_NAME(@@PROCID)

  PRINT @@PROCID
  print @sprocname

  insert into Diagnostics_Log VALUES (@sprocname, GetDate(), @Comment)

GO

--========== Then run this section

 exec dg_test_name_logging 'this is the first run'
WAITFOR DELAY '00:00:00:123'
  exec dg_test_name_logging1 'this is my second comment'
WAITFOR DELAY '00:00:00:123'
  exec dg_test_name_logging2 'yet another comment'
WAITFOR DELAY '00:00:02:123'
  exec dg_test_name_logging3 'amazing'
WAITFOR DELAY '00:00:00:123'
  exec dg_test_name_logging4 'I''ll be gobsmacked if this works'
WAITFOR DELAY '00:00:00:123'
  exec dg_test_name_logging5 'It''s ALIVE !!!'

  select * from Diagnostics_Log

Could be useful for logging when sprocs or views are run or accessed.