SQL Server 2008 – Is Order Guaranteed When Calling Two Stored Procedures?

sql-server-2008stored-procedures

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.

Best Answer

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.

Related Solutions

Sql-server – Returning Multiple Result-Sets or Single XML Blods

I prefer multiple resultsets because its faster and uses less memory and much easier to deal with.

But everything depends on consumer of the data - for example if it can receive only XML - you have no choice 8-)

PostgreSQL stored procedure which can return one of two custom rowtypes

Since your two types are composed from the same basic types in the same order, they are compatible with each other. So the return type will be the same for functions using them - if I understand correctly, your problem is you want to have the different names of the returned columns depending on the type.

This cannot be done from the database, but you can easily do it from your application. It can be done like (ugly pseudocode)

if type == 1 {
    execute("SELECT 1 AS id_normal_number, '00001' AS normal_number FROM your_function()")
} else {
    execute("SELECT 1 AS id_special_number, 'S0001' AS special_number FROM your_function()")
}

ie. changing only the names, but using the same DB function. Or you can invoke two different functions, this way you can avoid dynamic SQL, which may be desirable for readability/performance, depending on some circumstances.

Best Answer

Related Solutions

Sql-server – Returning Multiple Result-Sets or Single XML Blods

PostgreSQL stored procedure which can return one of two custom rowtypes

Related Question