I have some code that looks like this:

    EXEC StoredProcedure1 @Arguments

    EXEC StoredProcedure2 @Arguments

Are the two datasets guaranteed to return in the called order? I am expecting two result sets to be passed back to my server code and am running into some issues and I feel the ordering may not be guaranteed. If it is not guaranteed, is there a way to enforce ordering?

Not the ordering of the data, but ordering of StoredProcedure1 always being returned as result set 1, and StoredProcedure2 always being returned as result set 2.

  • I mean, this depends if both IFs are true or not. If both are, then the sp's will run in the order that you wrote them – Lamak Jan 2 '18 at 17:12
  • @Lamak There is a case where both IF's are true. I know the SP's will be run in the written order, but will the result sets be returned in that called order? For clarification I have removed those IF's bits of code, they are not really relevant to my question. – Hoser Jan 2 '18 at 17:12
  • Do you have a feeling its not guaranteed or have you actually seen them not return in the same order? It should be simple to test. – LowlyDBA Jan 2 '18 at 17:18
  • Would this not all depend on whether these were asynchronous or synchronous calls? – Nic Jan 2 '18 at 17:21
  • Well I am running into a very strange issue right now where the exact code and data is failing in certain environments and not in others. Where it is failing I do not have access to see the actual result data, so I am asking if order is guaranteed in this instance. – Hoser Jan 2 '18 at 17:21

Yes. StoredProcedure1 will execute first, and any resultsets from StoredProcedure1 must be consumed by the client before StoredProcedure2 is executed.

It's possible that some client driver or DAL processes all the resultsets and then returns them to your calling code in a different order, but for SQL Server and the Microsoft client drivers, you will get the resultsets in the order in which they are produced.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.