[RESOLVED] SQL Stored Proc Recursive Calls: Same Thread?

[rjbudz]

I have a stored procedure with action flags passed to perform certain operations:

If @nvchrAction = "DO_THIS"
BEGIN

-- do stuff

END

If @nvchrAction = "DO_THAT"
BEGIN

-- do stuff

END

Now, from DO_THIS I want to repeatedly call DO_THAT. It's obviously the same stored procedure, just a different copy I am invoking.

My question is, does each call run in a separate thread? If I change a value in DO_THAT will the DO_THIS operation be affected by it?

Thanks!

[mendhak]

No, it'll keep recursing based upon the circumstances and parameters available to it from your top level call downwards. Eventually it must RETURN and continue execution in the original stored procedure and exit the whole thing.

[rjbudz]

Thank you. So specifically:

Any parameters I pass to the DO_THIS call (from my application) will be available to the DO_THAT call (no need to pass variables which DO_THAT needs).

Any modifications I make to local variables in DO_THAT will be affected when I return to DO_THIS.

Is that right?

[mendhak]

Uhm... that was rather confusing.

But after reading it several times, yes. It is just like calling any normal method in any other language.

[rjbudz]

Thanks very much.

[rjbudz]

After doing further research into this, I've determined that the recursive call to the stored procedure (with the DO_THAT action flag in the above example) does NOT have access to the variables set in the calling one (DO_THIS in the example). If the call to DO_THAT requires parameters, they need to be passed in explicitly, such as

ALTER PROCEDURE  [dbo].[TestSP]
...

 @MyVar1 int = 0
 @MyVar2 nvarchar(3) = ''

...



If @nvchrAction = "DO_THIS"
BEGIN

exec dbo.TestSP  @nvchrAction = "DO_THAT", @MyVar1 = @MyVar1 , @MyVar2 = @MyVar2 

END

If @nvchrAction = "DO_THAT"
BEGIN

-- do stuff with MyVar1 and MyVar2

END

Otherwise they will use the default values.

I suspect, in light of this, that the variables are passed by reference and not value.

[mendhak]

My apologies, I had assumed you would be passing those parameters in. It was a "given" to me, you know what they say about assumptions.

[rjbudz]

No worries.

In the end I got my answer, and others may get the answer to their questions here in the future. And that's what it's all about.