Stored Procedure - forcing execution order

Question

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?

Edit for extra detail:

There is a table in my db which has a column which is null by default. prProc1 performs a set of update statements on this table to populate this column. prProc2 then inserts summary data into a secondary table based on the values in this column.

When I run the super procedure, what I am (sometimes) seeing is the first table has the results correctly calculated by prProc1, but prProc2 has generated results as though the column was all nulls. If I then manually run prProc2, the summary data is generated correctly.

Answer 1

Proc2 will not run before Proc1: it's a simple as that. SQL will execute one after the other but never out of order.

You can profile this using the TSQL_SPs template

Do you have 2 executions of the wrapper proc running, for example?

Answer 2

For each call of prSuperProc, they will run in a serial fashion, 1 then the next, then the next. However, if multiple users are all calling prSuperProc, then you will have a interleaved execution of user 1's prProc1-prProc2+prProc3 and user 2's prProc1-prProc2+prProc3.

which could be something like this:

user1 calls prSuperProc
user1          prProc1 is called
user2 calls prSuperProc
user1          prProc2 is called
user2          prProc1 is called
user1          prProc3 is called
user2          prProc2 is called
user2          prProc3 is called

it really depends on what is going on within your procedures, how many concurrent users, and what rows they are changing and/or locking

EDIT you can try this to fix the problem:

CREATE PROCEDURE [dbo].[prSuperProc]

AS
BEGIN TRY
    BEGIN TRANSACTION
    EXEC [dbo].[prProc1] 
    EXEC [dbo].[prProc2] 
    EXEC [dbo].[prProc3]
    --etc
    COMMIT
END TRY
BEGIN CATCH
    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION
    END

    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage


    --will echo back the complete original error message
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

END CATCH
GO

by using a transaction around everything, it will attempt to lock any concurrent users from working on the same data. Using the TRY-CATCH will attempt to catch any errors that might happen in one procedure and prevent the next ones from then running.

Answer 3

I had the same problem (was maintaining a product at that time) and I fixed it by taking out the outer most proc and executing the top most proc. Then that proc would execute the proc dependent on it and so on. Its a pain in the B*** but it works.

HTH