Stored Procedure - forcing execution order
- by meepmeep
I have a stored procedure that itself calls a list of other stored procedures in order:
CREATE PROCEDURE [dbo].[prSuperProc]
AS
BEGIN
EXEC [dbo].[prProc1]
EXEC [dbo].[prProc2]
EXEC [dbo].[prProc3]
--etc
END
However, I sometimes have some strange results in my tables, generated by prProc2, which is dependent on the results generated by prProc1. If I manually execute prProc1, prProc2, prProc3 in order then everything is fine. It appears that when I run the top-level procedure, that Proc2 is being executed before Proc1 has completed and committed its results to the db. It doesn't always go wrong, but it seems to go wrong when Proc1 has a long execution time (in this case ~10s).
How do I alter prSuperProc such that each procedure only executes once the preceding procedure has completed and committed? Transactions?
