Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using @@PROCID to return correct name of SQL Server procedures, functions and triggers


By:   |   Last Updated: 2014-07-08   |   Comments (2)   |   Related Tips: More > Stored Procedures

Problem

When I start coding a new procedure I frequently make the mistake of later renaming the procedure and neglecting to update the error messages and logging code that mentions the stored procedure name with the procedure.  How can I prevent such a simple mistake from happening as well as use a consistent process?

Solution

SQL Server, from version 2000 on, has included the @@PROCID function that returns the object id of the currently running procedure.  The word "procedure" used here includes stored procedures, user-defined functions and triggers.  To get the procedure name I always include the line:

DECLARE @this_proc_name sysname = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID))
                                    +'.'+QUOTENAME(OBJECT_NAME(@@PROCID))

Then when referencing the procedure name in a message I use the variable name instead of hard coding the procedure name like this:

CREATE PROC demo_an_error_message 
AS
DECLARE @this_proc_name sysname = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID))
                                    +'.'+QUOTENAME(OBJECT_NAME(@@PROCID))

DECLARE @msg varchar(255)

SET @msg = 'ERROR in ' + @this_proc_name + ' whatever the message should say'
RAISERROR (@msg, 16, 1)
GO

exec demo_an_error_message
GO

As you can see below, this displays the correct message:

Msg 50000, Level 16, State 1, Procedure demo_an_error_message, Line 9
ERROR in [dbo].[demo_an_error_message] whatever the message should say

Now when I decide that my stored procedure needs a better name and I rename it, the procedure name in the message remains correct.

exec sp_rename demo_an_error_message, new_msg_demo
GO

exec new_msg_demo
GO

This still displays the correct message:

Msg 50000, Level 16, State 1, Procedure new_msg_demo, Line 9
ERROR in [dbo].[new_msg_demo] whatever the message should say

By coding with @@PROCID it doesn't matter how many times a procedure is renamed, it will always return the correct name in the message.  I've made this a standard part of my coding practice to avoid a very common and misleading error.

Understanding what @@PROCID will return

There's more to the story because there is information out on the web that can mislead you about what @@PROCID can do.

I originally ran into @@PROCID when searching for a way to get the initiating procedure name from inside a trigger.  There are places on the web that will tell you that you can use @@PROCID in a trigger to get the object id of the stored procedure that did the DML that initiated the trigger and from @@PROCID you get the object name.  These sites are wrong, at least since SQL Server 2008 R2, and I think before. @@PROCID returns the object_id of the trigger that is running, not the procedure that executed the DML.  This script creates an example table and trigger to illustrate the problematic suggestion:

use tempdb
go
if OBJECT_ID('tempdb.dbo.my_data') IS NOT NULL drop table my_data
GO

create table my_data (a int NOT NULL
                    , b varchar(128) NOT NULL)
go

create table my_data_log (insert_datetime datetime not null default (getdate())
                         , [procedure] sysname NULL
                         , [user] nvarchar(128) NULL
                         , rows_changed bigint
                         )
go
                        
create trigger trg_my_data_log on my_data AFTER INSERT, UPDATE
as

insert into my_data_log ([procedure], [user], rows_changed)
values (QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID))+'.'+QUOTENAME(OBJECT_NAME(@@PROCID))
       , current_user
       , (select COUNT_big(*) from inserted)
       )
go

create proc updater_one as
insert into my_data(a, b) values (1, 'updater one is my proc')
go

create proc updater_two as
insert into my_data(a, b) values (2, 'updater two is my proc')
                                ,(3, 'updater two is my proc')
go

Now let's execute "updater_one" and see what happens:

exec updater_one
go
select * from my_data_log
go

The results are:

(1 row(s) affected)

(1 row(s) affected)
insert_datetime          procedure               user    rows_changed ------------------------ ------------------------ ------------------------------- 2014-06-29 06:16:24.820  [dbo].[trg_my_data_log]  dbo    1 (1 row(s) affected)

Right there in the middle of the results SQL tells us that the update was recorded by the trigger.  That's not particularly useful information. What we really wanted to know was which procedure made the change: updater_one or updater_two.  The only way that I know to get that is to have the update procedures write to the my_data_log table instead of using a trigger.  The next script ALTERs the updater_one stored procedure to do just that:

drop trigger trg_my_data_log
go

ALTER proc updater_one 
as
DECLARE @row_count BIGINT
      , @this_procedure_name sysname =  QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID))
                                    +'.'+QUOTENAME(OBJECT_NAME(@@PROCID))

insert into my_data(a, b) values (1, 'updater one is my proc')
set @row_count=ROWCOUNT_BIG()

insert into my_data_log ([procedure], [user], rows_changed)
VALUES (@this_procedure_name, CURRENT_USER, @row_count)
GO

Let's delete the bad data from the my_data_log table and run "updater_one" again:

delete from my_data_log
go
exec updater_one
go
select * from my_data_log
go

This time results are more satisfactory:

(1 row(s) affected)

(1 row(s) affected)
(1 row(s) affected) insert_datetime         procedure              user   rows_changed ----------------------- ---------------------- ------ -------------------- 2014-06-29 07:14:46.137 [dbo].[updater_one]     dbo    1
(1 row(s) affected)

Now we're getting the correct name of the procedure.

If someone were to come along and rename the procedure, we'd still get the correct procedure name.  Take a look:

exec sp_rename updater_one, upd_one_new_name
go
exec upd_one_new_name
go 
select * from my_data_log
go

The name of the procedure is correct:

Caution: Changing any part of an object name could break scripts and stored procedures.

(1 row(s) affected)

(1 row(s) affected)
insert_datetime         procedure                  user    rows_changed
----------------------- -------------------------- ------- --------------------
2014-06-29 07:14:46.137 [dbo].[updater_one]        dbo     1
2014-06-29 07:34:52.960 [dbo].[upd_one_new_name]   dbo     1

(2 row(s) affected)
Next Steps
  • Use @@PROCID when you want to get the name of a stored procedure, function or trigger to use in a message
  • Always be skeptical of anything you read in the internet. Try it out before using it.


Last Updated: 2014-07-08


get scripts

next tip button



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 23, 2014 - 7:37:25 AM - PRITESH Back To Top

Good one ..... 


Wednesday, July 09, 2014 - 12:29:08 PM - Gene Wirchenko Back To Top
No, not "As you can see". The code sections are displayed in small, light grey print. I can not read them. Please pick a better colour. Sincerely, Gene Wirchenko

Learn more about SQL Server tools