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 Procedure Procedure1
As
DECLARE @ProcName sysname
SET @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) +'.'+QUOTENAME(OBJECT_NAME(@@PROCID))
SELECT @ProcName
Execute SP:
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
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 Procedure Procedure1
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
No comments:
Post a Comment