Problem / Solution
The input parameter @@PROCID returns the object_id of the current stored procedure, function or trigger. This video shows how to use @@PROCID in a stored procedure to get the schema and procedure name. It also corrects a misperception that appears in some places on the internet that @@PROCID would let you know which procedure initiated a trigger. It doesn’t do that and that problem is demonstrated.
Key Learning Items
- Using @@PROCID with OBJECT_NAME
- Using @@PROCID with OBJECT_SCHEMA_NAME
- How to use @@PROCID to always return the correct name of the procedure
- What happens when you use @@PROCID inside of a trigger
Read Companion Tip
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Loadfest” event held in Waltham, MA.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2014
