Procedure, know thyself...

Anybody got a code snippet that will allow a stored procedure to determine its own name at execution?It gets real ugly real fast. I think only admins can run this code with no error. Any problems with just teaching each sproc its own name by hard-coding? That's how I learned my name, anyway ;-)

create procedure testproc
as
create table #temp
(col1 nvarchar(20),
col2 int,
col3 varchar(255))

insert into #temp
exec ('dbcc inputbuffer(@.@.spid)')

select col3 from #temp
drop table #temp
go

exec testproc|||Pat pointed me to this: SELECT OBJECT_NAME(@.@.PROCID)

Works well:CREATE PROCEDURE testprocedure AS
SELECT OBJECT_NAME(@.@.PROCID) AS 'ProcName'
GO
EXEC testprocedure
GO
drop procedure testprocedure|||I gotta ask...why do you need to do this?

I do sproc loggin in some systems, with a nested logging call, and still don't see the need for this|||For event logging.
One less hard-code value.|||CREATE PROC <procname>

well I guess it can be a resusable piece of code...make it part of tql template I guess|||This code goes at the top of the procedures I want to monitor:
--Log Event------------------------------
declare @.EventText varchar(500)
declare @.ProcedureName varchar(50)

set @.ProcedureName = OBJECT_NAME(@.@.PROCID)
set @.EventText = @.ProcedureName + ' '
+ dbo.LogParam('@.Param1', @.Param1)
+ dbo.LogParam('@.Param2', @.Param2)
...
+ dbo.LogParam('@.ParamN' ,@.ParamN)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LogEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
exec LogEvent @.ProcedureName, @.EventText
-----------------------------------

If I get a chance, I'm going to look for a way to generate the parameter names and values automatically as well, though that would involve dynamic code.
This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.|||SELECT name FROM dbo.syscolumns WHERE (@.@.procid) = id-PatP|||Oh yeah, get chummy with QuoteName() too.

-PatP|||This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.

That has got to be a bad idea...if it's a low OLTP, then maybe...but why do you care

I mostly care about data changes with history...next would be performance|||This is for debugging. So when the developer comes to me and says "your stored procedure returns the wrong data", I can say "Your code submitted the wrong parameters".|||How about DECLARE @.func VARCHAR(50)
SELECT @.func = Object_name(@.@.Procid)as in ErrorHandler:
DECLARE @.func VARCHAR(50)
SELECT @.func = Object_name(@.@.Procid)
EXECUTE [dbo].[ErrorHandler] @.m_error, 'MyDBIndex sp', @.func
RAISERROR ('Stored Procedure %s failed with error number %d', 16, 1, @.func, @.m_error)|||Dang, just when I thought smugly "I know this one!", I get scooped. damn. Back to the mines I go...|||I'm too lazy to modify code... SQL Profiler will give me all the evidence I need to abuse the lazy, without requiring anything more on my part than simply turning SQL Profiler on and watching the fun.

-PatP|||I like my code because I am lazy.
I don't have to have profiler running constantly to catch the executed sprocs, and I can copy and paste the code directly from my eventlog table into query analyzer to reproduce the issue.|||I'm too lazy to like my code, which I am too lazy to write just to catch lazy developers.