Stored Procedure - Order Fulfillment

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, sometimes I get some strange results in my tables generated by prProc2, which depends on the results generated by prProc1. If I manually execute prProc1, prProc2, prProc3, then everything is fine. It seems that when I run the top-level procedure, Proc2 is executed before Proc1 has completed and passed its results to db. This does not always happen incorrectly, but it does not seem to be the case when Proc1 has a long run time (in this case ~ 10 s).

How to change prSuperProc so that each procedure is executed only after completion and execution of the previous procedure? Deals?

Change additional information:

db , null. prProc1 , . prProc2 .

-, , () , - , prProc1, prProc2 , . prProc2, .

Answer 1

Proc2 Proc1: . SQL , .

TSQL_SPs

2 ?

Answer 2

prSuperProc : 1 , . , prSuperProc, 1 prProc1-prProc2 + prProc3 2 prProc1-prProc2 + prProc3.

:

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

, , /

, :

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

, . TRY-CATCH , , .

Answer 3

I had the same problem (supported the product at that time), and I fixed it by pulling out the outer part of proc and running the largest part of proc. Then this proc will execute its dependent proc, and so on. His pain is in B ***, but it works.

NTN