EXEC Stored Procedure inside another doesn't wait to to finish

Question

Hi,

I have a stored procedure which executes another procedure inside it. The second one sometimes takes a while to run but the first seems to finish before waiting for the second. This has resulted in missing data, which should have been updated by the second procedure. Is there a timeout limit within the first procedure and can this be extended?

Answer 1

I would suspect that the inner stored procedure is in fact finishing/exiting as stored procedures run sequentially and symmetrically.

You might try scripting the outer stored procedure and drop it into Management Studio, remove the Create Procedure declaration, replace the arguments with Declare and add SET statements to set them to the values you are using on your test run. In addition, put Print statements after the call to the inner stored procedure and see if it completes.

Answer 2

You are understandably mistaken. Stored procedures execute synchcronously. For a variety of reasons, however, the results of the inner procedure may not be visible to an external process until after they have actually occurred. This may be why you are seeing whatever you are seeing that leads you to belive the order of completion is not in synch.

Answer 3

create a new table:

LogInfo
LogID     int auto number/identity primary key
LogDate   date default current date and time
LogValue  string

in each procedure add INSERTs like this:

INSERT INTO LogInfo (LogValue) VALUES ('starting procedure A')
...
INSERT INTO LogInfo (LogValue) VALUES ('Calling procedure B')
...

INSERT INTO LogInfo (LogValue) VALUES ('ending procedure A')

then do this

SELECT * FROM LogInfo ORDER BY LogID 

to see what happened, hopefully you will see this because procedures run sequentially and B can not finish after A:

starting procedure A
Calling procedure B
starting procedure B
ending procedure B
ending procedure A