May 17, 2008 at 7:16 am
Hi,
With the TRY .. CATCH block, you can determine the procedure name with ERROR_PROCEDURE(). Unfortunately, this function only returns data when there's an error. How can I determine the procedurename without having an error?
I want to use this for batchlogging like:
insert into processlog
values ERROR_PROCEDURE(), CURRENT_TIMESTAMP
(Yes I know, this example is'nt working) :crazy:
thanks!
Wilfred
The best things in life are the simple things
May 17, 2008 at 8:25 pm
I don't think that you can get this.
I always just set a variable at the beginning of my stored procedures:
Declare @ProcName varchar(64)
Set @ProcName = 'spMy_SprocName'
...
Print @ProcName
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2008 at 3:58 am
Yes, that's the way I do it right now. But I think there must be a way to determine the procedurename. If SQL can tell you the name if there's an error, why not when there's no error?
Wilfred
The best things in life are the simple things
May 18, 2008 at 10:00 am
You can determine the object name with the following:
SELECT object_name(a.objectid)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE session_id = @@spid;
If you need/want the schema also, you have to join in sys.objects to get the schema_id and then you can use SCHEMA_NAME(schema_id) to get the appropriate schema.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2008 at 11:52 am
Nice one, Jeff.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2008 at 12:16 pm
Actually just found a different approach:
SELECT object_name(@@procid);
-- with SCHEMA
SELECT schema_name(schema_id) + '.' + object_name(object_id)
FROM sys.objects
WHERE object_id = @@procid;
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2008 at 2:09 pm
great work! thanks
Wilfred
The best things in life are the simple things
May 19, 2008 at 7:56 am
Great news. Microsoft made it simple to get the procedure name and this forum helped me find the answer within minutes. You guys rock.
May 19, 2008 at 8:08 am
As simple as it looks like....
SELECT OBJECT_NAME( @@PROCID )
--Ramesh
May 19, 2008 at 9:17 am
jeff.williams3188 (5/18/2008)
Actually just found a different approach:
SELECT object_name(@@procid);
...
Heh, NOW I remember it, I have used this before. The funny thing is every time I try to remember it or find it in BOL I lose it again. I suspect it's one of those mental short-circuits where I see "@@PROCID" in the BOL index and I think "that's ProcessID, not ProcedureID." Which is silly of course because I know perfectly well that @@SPID is the process ID.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2008 at 3:51 pm
I know exactly what you mean - I knew about this but couldn't find it, so wrote my own. Then, of course - I found it again. 😀
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply