All Forums General
boriscb 22 posts Joined 09/13
03 Mar 2014
Get the Stored Procedure name currently executing

Hello,
 
I need to identify the current stored procedure to update a Log table.
In MSSQL I would be using the following command:
SET @vProcName = OBJECT_NAME(@@PROCID) ;
Is there an equivalent in Teradata ?...
Thank you for your assistance.
Boris.

krishaneesh 140 posts Joined 04/13
03 Mar 2014

you can verify all the queries that are currently being executed using the viewpoint or the performance monitor. if you want to know those which were executed today you can verify the DBQL tables in DBC database, if the DBQL has been enabled. DBC.DBQLObjTBL holds all the objects that were accessed today. select * from dbc.dbqlobjtbl where objectdatabasename=<databasename> and objecttablename=<procedure name>

boriscb 22 posts Joined 09/13
03 Mar 2014

Hi,
Thank you for your response. However, my question is how can I obtain the SP name within a SQL stored procedure with a sql statement of some sort, so I can log the start/end, error etc... of that sp process dynamically ?.. This is to avoid hard coding the name in the SP itself...
Thank you.
Boris.
 

Raja_KT 1246 posts Joined 07/09
03 Mar 2014

---Avoiding hard coding of procedure , if there is a call of procedure inside another one ...... I dont know how the tracking is done
Probably you may need more info too besides proc name.Will it not be more handy to code the logic inside the procedure itself, logging the information you want in a tracker table such as procedure name, status,start time, end time etc. Get the status by looking at start and end time.

It can be something like this :
SET Currently_Running = 'Procedure abc running' ;
......
Then log info into a tracker table, where you can use it any time.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

boriscb 22 posts Joined 09/13
04 Mar 2014

Hello,
Thank you for your feedback. That is "plan B".
There could be 100's of SPs, so the idea is to use a generic command that would retrieve the name of the SP when it is executed rather than have the name hardcoded in the SP. If the SP name was to change, (e.g.: mysp_v1.2 => mysp_v1.3 etc...) the code would take care of getting the current name without having to remember to change it manually.
BTW there is a standard command in MSSQL for this.
Thank you.
Boris.
 
 

You must sign in to leave a comment.