what stored procedure called a stored procedure

Question

what stored procedure called a stored procedure

dough-378918

Right there with Babe

Points: 716
More actions
September 16, 2010 at 1:54 pm

#238402

If I'm in a particular stored procedure, I'd like to be able to get the name of the stored procedure that called this one.
Is there a system function or something that would have this info?
TIA,
Doug

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

A quick method is the right-click the stored procedure and click View Dependencies. It will show you the information you are looking for.

WayneS SSC Guru Points: 95460 More actions · Answer 2

if you want to do this at run-time, you'll need to add a parameter (@Proc_ID int), and when calling the procedure pass in @@PROCID.

You can then

select object_name(@Proc_ID)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

dough-378918 Right there with Babe Points: 716 More actions · Answer 3

I do want to do this at run-time and I'd like to avoid passing a parameter.

In short, I'm considering a security type of stored procedures that other stored procedures call. The security one will check what application is running the stored procedures and verify the calling stored procedure can be used by the particular application.

I'd prefer not to pass in a parameter as I will expose the text of the 1st stored procedure to the programming staff. The security stored procedure will not have the code exposed. Using a parameter exposes some of the info the security SP is checking against.

Since when you do a Return, the system knows who to send the return info to, I'm assumming somewhere while the stored procedures are running there's info in the system that is tracking who called the security stored procedure. I am hoping to be able to tap into that info.

Thanks,

Doug

WayneS SSC Guru Points: 95460 More actions · Answer 4

Doug - I sure wish you had given this depth of information at the beginning - it tells exactly what you're looking to do. With the first post being vague, you can see how the answers popped up all over the place.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2