Problem:
When I write any procedure for UI functionality, I may require to throw the Procedure Name to UI if any error occurs, This will help people to identify/Isolate the bug if occured
Also I Dont want to hard code the procedure name in the SPs because sometime I/someone may change the SP Name in future.
How can I prevent such a basic mistake as well as use a consistent process?

Solution:
SQL Server has a function called @@PROCID, If you use this function inside of any procedure/function/trigger, then it will return the objectid that currently running. below snippets will help you how to use this function.

Sample SP:
Create ProcedureProcedure1 
As 
DECLARE @ProcName sysname 
SET @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) +’.’+QUOTENAME(OBJECT_NAME(@@PROCID)) 
SELECT @ProcName 

Execute SP:

Exec Procedure1 

Output:
[dbo].[Procedure1]
@@PROCID Function is available from SQL 2000 onwards. This Function return Object ID, use OBJECT_NAME Function to get the Name of the Object as mentioned in the script given above.

Next Steps:
Refer the below documentation to understand complete functionality of this function as well as how to use it in different ways

MSDN: @@PROCID (Transact-SQL)
TechNet: @@PROCID

Categories: SQL

Related Posts

SQL

Cannot login to SSAS 2008 from SSMS?

Issue: When I tried to connect to a named instance of SQL Server 2008 Analysis Services (SSAS) by using SQL Server Management Studio, I received the following error message intermittently:

SQL

How To: Lists Linked Server

The following Stored procedure will returns the list of linked servers defined in the server in which we are executing it. sp_linkedservers This procedure will return the following informations about the linked server Column name Read more…