what stored procedure called a stored procedure

  • 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

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

  • 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

  • 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

  • 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

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

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