Stored procedure not being executed within another

Question

I have found that SP2 doesn't execute from within SP1 when SP1 is executed.

Below is the structure of SP1:

ALTER PROCEDURE SP1 AS BEGIN

Declare c1 cursor....

open c1 fetch next from c1 ...

while @@fetch_status = 0 Begin

...

Fetch Next from c1 end

close c1

deallocate c1

exec sp2

end

---

I see non of the PRINT statement outputs if they are printed in the 'Output window' in SQL Server 2005 management studio as the 'Output Window'is empty.

Answer 1

What happens if you run the Stored Procedure code as a single query? If you put a PRINT statement before and after the exec, do you see both outputs?

• If you do, then the stored procedure must have been executed. Probably it's not doing what you would like.
• If you don't see any print output, then there's something wrong in the cycle
• If you don't see the second output but you see the first, there's something wrong in the second Stored Procedure.

Answer 2

you could use @@error to see if there was an error when executing the previous statement.

Answer 3

I am not sure if it helps you, but from my experience the most popular reasons are:

1. sp2 gets some parameter which makes it null value -- i.e. you build its name from the strings and one of them is null.
2. sp2 has some conditions inside and none of them is true, so sp2 executes no code at all -- i.e. one of the parameters is type varchar, you pass value VALUE, check for it inside, but the real value passed to sp2 is V (because there are no varchar length defined).
3. sp2 builds query from parameters where one of them is null and the whole query becomes null too.

Do you see any output if you put PRINT before and after call of sp2?