By: Andy Novick | Updated: 2014-07-08 | Comments (2) | Related: > 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
GOAs 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)
GOLet'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.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-07-08
