January 25, 2008 at 1:25 am
I am calling a function from two separate stored procedures.
In the function I need to identify who is the calling SP so that I can execute conditional scenario something like this in the fucntion:
if caller = SP1 then
Select total from t1
else
Select total from t2
Any help/suggestion on this would be highly appreaciable. (facing a stiff design situation)
Thanks.
January 25, 2008 at 5:57 am
I've never heard of such a thing. You could simply add a parameter to your function and pass in the calling procs name. It'll be interesting to see if someone else has a solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2008 at 6:00 am
I did just a bit more searching. I wasn't sure if it was possible to identify the proc you're in, let alone that proc that called you. If you look up @@PROCID, it returns the id value of the procedure you're running within. You can then use it with the OBJECT_NAME() function to get the name of the procedure you're currently within. I don't think it will do what you want though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2008 at 8:35 am
You might want to check DBCC InputBuffer in BOL and the text of SP_WHO to see if you can crib some code from the procedure to help you identify the SPID of the caller. Once you have the SPID, you should be able to use the sys.Server_principals catalog to identify the caller.
Best way, though, would be to implement role-based security via a table in the database and the application code so you more tightly control this functionality without having to update the Function everytime someone left / joined the company.
January 25, 2008 at 10:14 am
If the persons calling this aren't dbo's, why not define user-specific functions or prcedures for them.
a.k.a. bob.myfunction(), jody.myfunction(), etc...?
Or - instead of checking by person, perhaps use the IS_MEMBER('groupname')? That would be a whole lot more maintainable...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply