Stored Procedure Question

Posted in 2015

Topics: Stored Procedures & SPL, Security, Permissions & Auditing

Mitchell, Jeffrey J. — Wed, 01 Apr 2015 20:17:15 — source: IIUG Forums & Mailing Lists

I've been trying to find a way to use a built-in variable (similar to USER) to give me the name of the procedure that is currently running (to be used as a value inserted into an audit table). Is there a way - from within a running stored procedure - to find out the name of the procedure that's currently running? Kind of a circular question, but it will have to do for now. [Description: image001]Jeffrey J. Mitchell Database Administrator - Interactive Services 402-716-0500 | Cell 402-321-7443 | jjmitchell@west.com<mailto:jjmitchell@west.com> West Corporation, 11650 Miracle Hills Drive, Omaha NE 68154 This electronic message transmission, including any attachments, contains information from West Corporation which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message and destroy all electronic and hard copies of the communication, including attachments.

↪ replying to Mitchell, Jeffrey J.

Fernando Nunes — Wed, 01 Apr 2015 23:17:15 — source: IIUG Forums & Mailing Lists

Why don't you activate the audit facility? It can capture any stored procedure execution and it's very easy to do. Apart from that it should be possible to capture the current statement from sysmaster views and with a bit of parsing you could possible extract that information. Are you considering "EXECUTE PROCEDURE PROC_NAME()" or "SELECT PROC_NAME()..." or both? Regards On Wed, Apr 1, 2015 at 9:17 PM, Mitchell, Jeffrey J. <JJMitchell@west.com> wrote: > I've been trying to find a way to use a built-in variable (similar to > USER) to > give me the name of the procedure that is currently running (to be used as > a > value inserted into an audit table). > > Is there a way - from within a running stored procedure - to find out the > name > of the procedure that's currently running? > > Kind of a circular question, but it will have to do for now. > > [Description: image001]Jeffrey J. Mitchell > Database Administrator - Interactive Services > 402-716-0500 | Cell 402-321-7443 | > jjmitchell@west.com<mailto:jjmitchell@west.com> > West Corporation, 11650 Miracle Hills Drive, Omaha NE 68154 > > This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The > information is intended to be for the use of the individual or entity named > above. If you are not the intended recipient, be aware that any disclosure, > copying, distribution or use of the contents of this information is > prohibited. > > If you have received this electronic transmission in error, please notify > the > sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c2e34231f9c60512b1ea81