Stored proc calling another stored proc with output?

[jtseltmann]

I have one main stored proc that sets a table variable and then loops through the records. After setting the vars I call my other proc that has an output param I need.

As I perform the loop...the input to the 2nd proc changes and I expect the output param to change was well. I can verify this also by printing my vars. It seems the call to the proc to get the output is happening once. One of the records in my set should return an error text and it isn't. If I make my loop select only pull that one record then it works but if it pulls all 150 it doesn't.

I have tried everything from calling the proc and hard coding the values. When I do this by itself, it works and the error text is returned. When I add it back and call the proc as I loop through records it never finds the bad rec.

I'm relatively new to sql proc programming but I cannot figure this one out. I can post sample code if you like. My code works perfectly except for the one var I set using the output from this second proc.

Is there some problem calling a proc within proc? NO right?

ANy help or suggestions would be great! Thanks!

 

[markros]

I think you may want to post some of your code. In the meantime, I suggest you to look for sp_ExecuteSQL in BOL.

You can also check this link

Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec


PluralSight Learning Library

 

[jtseltmann]

Thank you..i figured. The problem I found was that it seems SQL requires that you reset the output parameter to '' before each call to the proc that you want it returned from.

e.g. in the proc below if you don't reset @output1 = '' before each call the param is not set. It is the same value as the first time you called no matter what you do to the input parameter.

---------------------------
DELCARE @output1 varchar(30)

WHILE @rowcount <= @totalrows
BEGIN
SET @input1 = 'something'
SET @output1 = '' --<this is what I was missing
EXEC sp_getinfo @input1, @output1 OUTPUT
END
END
-----------------------------