SQL Server 2005 stored proc execution

Question

Using SQL Server 2005:

I have one stored proc that calls several others within it. One code segment, as an example, goes:

INSERT INTO Log (...) VALUES (A...)
EXECUTE StoredProcA params...
INSERT INTO Log (...) VALUES (A...)

INSERT INTO Log (...) VALUES (B...)
EXECUTE StoredProcB params...
INSERT INTO Log (...) VALUES (B...)

It appears that StoredProcB is executing before StoredProcA has completed execution. How can I ensure that A has finished before B starts?

The best solution that I can come up with right now is putting a call to B at the end of A. Any other ideas?

Answer 1

It will not happen. SQL is synchronous through code.

What makes you think it's happening? Log table entries?

Answer 2

one statement won't start until the previous is done..either use profiler to trace so that you will confirm this or put print statements around the calls. You will see that SQL Server won't continue until it has finished with the previous line

Answer 3

Maybe there are two or more separate processes (connections) executing the stored proc at the same time and one finishes before the other. Is this code executed as a transaction?

Answer 4

What exactly do you use to measure which procedure executed first? You have a bunch of inserts in the Log table, but inserts are no indication of execution order since tables have no inherent order, order is only created by a query using an ORDER BY clause, hence you can never tell from two records in a table which was inserted 'first'.