January 24, 2013 at 9:07 am
Hi guys,
I have a procedure which can be invoked through other procedure and I can run it outside. Is there some system function or DMV that I can discover its origin?
Depending on situation, that procedure must work different way.
Best regards
Ulisses
January 24, 2013 at 9:13 am
You could add an additional input to the SP and pass the name of the SP calling into it (OBJECT_NAME(@@PROCID))
January 24, 2013 at 9:13 am
AFAIK there's no any accurate way to do that.
I guess IF every caller used a different login or sql user, you could tell from that, but overall I think that's a shaky idea.
You could either:
1) set a control byte(s) in CONTEXT_INFO to identity the caller
2) add a param to the proc to pass in a caller id/code/name/etc. to identity the caller
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 24, 2013 at 8:42 pm
ScottPletcher (1/24/2013)
AFAIK there's no any accurate way to do that.I guess IF every caller used a different login or sql user, you could tell from that, but overall I think that's a shaky idea.
You could either:
1) set a control byte(s) in CONTEXT_INFO to identity the caller
2) add a param to the proc to pass in a caller id/code/name/etc. to identity the caller
Some info on Scott's suggestion #1:
http://qa.sqlservercentral.com/articles/context_info()/74332/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 24, 2013 at 9:27 pm
You may use @@NESTLEVEL.
If the proc is called from outside @@NESTLEVEL will be = 1.
If it's called from another procedure it will be 2 or more.
_____________
Code for TallyGenerator
January 26, 2013 at 1:06 pm
It sounds like a sketchy design because you want a proc to behave differently based in some implicit condition, namely who called it. Consider how you would unit test something like that. If you have one proc you want to act two different ways based on the caller then you should consider refactoring your proc into more than one proc and have the caller call the appropriate one based on context. If there is common processing logic abstract it and use it from both of the refactored procs.
If that route does not work out, then I would explore the two options mentioned, either modify the input interface to add a parameter or set something in the context info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply