February 6, 2009 at 4:58 am
Hi
I have two procedures
a) Procedure A
b) Procedure B
By design, Procedure B calls Procedure A internally. However, Two procedures can be executed independantly .
I want to findout that if procedure A is called
- Is it called from Procedure B or
- is it called independantly ie not from any other procedure
Your reply will help me a lot
Thanks
Venkat
February 6, 2009 at 6:10 am
The first answer that springs to mind is to add a parameter to stored proc A:
CREATE sprocA @calledby char(1)=NULL AS...
Then when you call it from sprocB, call it by using " EXEC sprocA 'B' ", while when calling it independently, use " EXEC sprocA ". You can then interrogate parameter @calledby to determine whether sprocB called it.
Hope this helps.
February 6, 2009 at 6:15 am
Venkat (2/6/2009)
HiI have two procedures
a) Procedure A
b) Procedure B
By design, Procedure B calls Procedure A internally. However, Two procedures can be executed independently .
I want to findout that if procedure A is called
- Is it called from Procedure B or
- is it called independently ie not from any other procedure
Your reply will help me a lot
Thanks
Venkat
If Procedure B creates a local temporary table (example #MyHead) prior to calling Procedure A, you can test for the exitance of the temporary table as Procedure A will be able to see and use the local temporary table internally. If called independent of Procedure B, this table would not exist.
The only other way that I can think of would be a optional parameter to Procedure A that would be used when calling Procedure A from within Procedure B and left null when called independent of Procedure B.
February 6, 2009 at 6:25 am
I wouldn't recommend doing this on a production system, but you could capture statement completion events from a trace. Then you'll see when the call is made from proc b.
However, does proc B always call proc A? If so, just use a trace to capture the executions of Proc B.
"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
February 6, 2009 at 6:33 am
Should have asked this first, why does Procedure A need to know if it was called by Procedure B or independently of Procedure B?
February 6, 2009 at 8:23 am
You could try looking at @@NESTLEVEL is you know definitely that either A is called directly or B calls A. Of course, if a hypothetical procedure C also calls A then the level won't help.
See http://msdn.microsoft.com/en-us/library/aa933163(SQL.80).aspx
Derek
February 7, 2009 at 3:59 am
February 7, 2009 at 9:17 am
Okay, Venkat, you have had several replies to your question as well as I asked of you. How about some feedback?
February 9, 2009 at 6:36 am
Hi All
Thankyou verymuch for the solutoins.
I tried using @@Nestlevel and it looks good to me
I implemented the same and tested as well.
Thanks a lot
I can now close this issue. 😀
Venkat
February 9, 2009 at 6:49 am
You may close the issue, but you failed to answer the question, why you needed procedure A to know if it was called by procedure B or called independently of another stored procedure.
It is only polite to answer a question when asked.
February 9, 2009 at 6:55 am
Hi Lynn
Sorry, I did not answer your question.
The reason I looked for is as below
1. I need to perform certain validation on database if it is called
independantly.
2. If it is called from any parent procedure, the validation may not
be required.
Previously I was passing a parameter Y/N.
Now by using the @@NESTVALUE , I am able to achive this.
Thanks, 🙂
Venkat
February 10, 2009 at 10:11 am
Thank you. Now it all makes sense and using @@nestlevel does look like the best option in this case.
Something new learned, good that is.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply