How do I find the calling stored procedure/function

Question

We have implemented a global error logger for all SQL Stored procedures and functions. When a procedure/function encounters an SQLEXCEPTION or SQLWARNING, we call a global error logger and execute a GET STACKED DIAGNOSTICS statement. Part of the data we need to have is the procedure name that failed. I am not seeing that in the GET DIAGNOSTICS available return values (ROUTINE_NAME and ROUTINE_SCHEMA are returned conditionally, and I need it always).

We are currently using the QWVRCSTK API, but I would assume that SQL has a method if providing this data. Anyone know how I can do this?

for SQL server, it will help http://msdn.microsoft.com/en-us/library/ms175976.aspx — Arun Singh, Jun 18 '12 at 15:34
See http://stackoverflow.com/questions/2086973/recording-sql-server-call-stack-when-reporting-errors SQL does not lend itself well to exception handling. You are handicapping yourself by not doing it client side. — Andomar, Jun 18 '12 at 15:39
SPECIFIC_SCHEMA may have what you're looking for but it too is only returned conditionally. I myself use the call stack API. — Buck Calabro, Jun 18 '12 at 16:11
Thanks you all, we actually just had the calling procedure pass in its name. Not ideal, but simple enough. — jgriffin, May 20 '13 at 15:14

score 0 · Answer 1 · edited Jan 06 '14 at 06:56

0

select @@ProcID

Returns the object identifier(ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger.

From there you can :

select OBJECT_NAME(@@ProcID)

Which will return the name of the object.

.

edited Jan 06 '14 at 06:56

Dhanish Jose

739
1
8
19

answered Apr 20 '13 at 18:16

Jay Pondy

176
2
11

The question is about DB2 on IBM i. – Buck Calabro Apr 20 '13 at 21:34

score 0 · Accepted Answer · answered Apr 13 '14 at 10:41

The Retrieve Job Information (QUSRJOBI) API can return the qualified name of the object that is running the current (most recent) SQL statement in a job. Use format JOBI0900 to access a job's SQL information.

You'd want to create an external stored procedure to call the API. (If you try to do it as a SQL stored proc, the problem would be that a statement in the SP itself would be the current one; and you want the current SQL to be in the calling proc.)

When this SP is called, the "current" SQL should be the CALL statement from back in the calling procedure. (I hope that makes sense.)

How do I find the calling stored procedure/function

2 Answers2