Retrieving the Gazoutas: Understanding SQL Server Return Codes and Output Parameters
Summary: Discusses how to capture, interrupt, and handle resultsets and rowsets, as well as the extra information that they return when executing a Microsoft SQL Server query. (7 printed pages)
How Are Queries Processed?
When executing queries or calling stored procedures, it's important to know how and when to fetch the other stuff returned by Microsoft SQL Server besides the rowset. One question keeps popping up on the newsgroups: "How can I tell how many rows are going to be returned by my query?" This is like asking farmer Dan exactly how many eggs his chicken laid overnight—he won't know until he goes out to the coop and collects them. However, farmer Dan does have a rough idea about how many eggs were laid. He knows that Sally is doing pretty well and should lay two and Betty is getting old and might need to be this Sunday's dinner, and so on. In this same vein, SQL Server won't know how many rows your query is going to return until it collects all of the rows, but you should (by design) have a pretty good idea how many rows your query will return.
How Are Queries Processed?
Folks seem to think that SQL Server completes your query and packages up the rows like so many eggs in a carton before sending them down to your client application. 'Taint so. SQL Server starts sending rows to your client (assuming your code is asking for them) as soon as the first rows are spewed out of the last step of the query plan. A few thousand KB worth of row data is buffered up in memory and waits until you ask for more. If you're using the ADO.NET DataReader, that means using the Read method to fetch another row. The Fill method does this for you, asking for rows as fast as SQL Server can send them down. One of the last things to get sent is the rowcount—as if you didn't already know. By this time ADO already knows, but it might be too late to do anything about it if you're trying to build an array big enough to hold the expected rowset before the first row arrives.
Counting Eggs and Rows
One approach is to have SQL Server return a count of the rows using a COUNT * query. However, this is like asking farmer Dan to go out to the coop and count the eggs before actually collecting any, and since he has about 60 chickens, there's a good chance that one or more of the hens will lay another egg or break one after he counts but before he begins to collect. So using COUNT * without a repeatable-read transaction might yield only an approximation. Not only that, the COUNT * approach requires SQL Server to repeat much of the processing of the rowset-returning query and the construction of a second resultset, both of which result in a loss in performance.
I think the best way to deal with this situation is through a mix of common sense and experience. That is, if you can't limit the number of rowset members with a TOP expression or WHERE clause (you should), you can make an intelligent guess at how many rows are expected based on past experience. You can either construct an array based on that estimate (and stretch the array when it fills), or better yet, in .NET Framework CLR languages, you can use an "ArrayList." The capacity of an ArrayList is the number of elements the list can hold. As elements are added to an ArrayList, the capacity is automatically increased as required through reallocation. The capacity can be decreased by calling TrimToSize or by setting the Capacity property explicitly.
Capturing the Gazoutas
But I digress. The point of this article is to discuss how to capture the extra information that SQL Server returns when you execute a query. In this context, you might try to return the COUNT * value or @@RowCount in a stored procedure RETURN value, but remember SQL Server won't know the answer until all of the rows are processed and sent to the client. This means the RETURN value is sent as one of the last items returned to the client at the end of the resultset, which is far too late to pre-configure an array. This also means that you'll have to process all of the rowsets returned by the stored procedure before any of the non-rowset gazoutas are available. For those of you that aren't familiar with the term, a gazouta is a term that I use to refer to anything that gets sent out of a stored procedure. This includes the RETURN value and OUTPUT parameters, as well as the rowset.
Whenever you execute a stored procedure (and some ad hoc queries), SQL Server generates one or more resultsets. Don't get this confused with an RDO Resultset object—it's related but not the same. Each resultset might contain a rowset and perhaps a packet that contains the @@ROWCOUNT (rows affected) value. When you execute a SELECT statement or an action query (INSERT, UPDATE, DELETE), SQL Server sets the @@ROWCOUNT to indicate the number of rows affected and returns this value in the last packets sent back to the client. That is, unless you put SET NOCOUNT ON in your stored procedure, in which case this packet is not sent. This option makes sense if you don't care about the number of rows changed by the latest action query. It helps performance too as ADO does not have to process the extra (empty) resultset.
Select Query Resultsets
So, let's take a closer look at the resultsets that get sent back from a query. A simple SELECT resultset contains both a rowset and a Rowcount value. Your code has to deal with the single rowset and capture the Rowcount if needed. Remember, you'll have to process each of the rowset rows before the Rowcount is returned from the server.
Action Query Resultsets
When you execute an action query (such as an INSERT statement), the resultset contains no rowset at all—just the Rowcount value. If you don't care about resultsets from action queries, you can eliminate them by including SET NOCOUNT ON in your stored procedures, but if ADO.NET is depending on this count, (as when you use the DataAdapter Update method), your action Command objects must return this value to properly manage concurrency.
Handling More Complex Queries
In more complex queries, several resultsets can be generated. Each SELECT statement and each action command generates its own resultset. If you use the DataAdapter Fill method to execute the query and process the resultsets, you don't have anything to worry about. ADO.NET handles all of the details of managing these resultsets as each resultset containing a rowset is used to construct a DataTable in the DataSet. The Rowcount is ignored for the most part, but for rowset-returning queries it can be accessed by checking the DataTable Rows Count property as follows:
Using a DataReader to Process Resultsets
Handling multiple resultsets with a DataReader is not that tough, however there are a number of differences if you're used to doing this in ADO classic. To illustrate, let's start with a made-up query that returns six resultsets—some with rowsets and some without. The code uses the Microsoft Visual Basic? .NET StringBuilder class to concatenate the individual queries into a single string for better performance.
Listing 1. Constructing the batch query
This SQL batch could have been a part of a stored procedure or simply executed stand-alone. No, not all query engines accept multiple-statement batches (Access/JET, for example), but it is allowed in SQL Server. As a matter of fact, submitting this multi-query batch eliminates five round-trips to the server to execute the individual queries. It also means that the resultsets are all threaded together in line to be processed one at a time. Next, we have to execute the query and prepare to process each resultset in turn.
What Happened to the Action Commands?
An interesting point to note is that both the DataAdapter Fill method and the DataReader ExecuteReader methods appear to skip over the action commands. No, they're actually executed and their resultsets are returned, it's just that ADO.NET does not worry you with processing the resultsets. As these action queries are sensed, the DataReader.RecordsAffected value is reset. That is, when the query begins the RecordsAffected value returns -1 if a SELECT query is executed. Once an action query resultset is sensed, RecordsAffected is set to N to reflect the last Rowcount value found in the resultset. Because the action queries are handled for you, all you have to worry about as far as your code in concerned is what to do with the rowsets.
Listing 2. Executing the batch and parsing the resultsets
The next snippet of code is used to process the rowset returned through the DataReader—row-by-row, column-by-column.
Listing 3. Processing the rowset returned by the DataReader
Managing Return Value and OUTPUT Parameters
If you want to capture the integer passed back by the RETURN statement in TSQL or your stored procedure's OUTPUT parameters, you're going to have to take another few minutes to setup a Command object to capture these values from the resultset. Again, and I can't emphasis this enough, these values are not made available until you've processed all rowsets in each resultset (if there are any). Yes, a stored procedure can return several resultsets, but the RETURN value and OUTPUT parameters are not populated until all queries have been completed and all rowsets transmitted back to the client.
Handling Unwanted DataReader Rows
Okay, so you asked for too many rows in one of your queries and you want to step over this rowset to get to your OUTPUT parameters when working with a DataReader. Don't be tempted to simply close the DataReader without using the Cancel method on the Command first. That's because ADO.NET completes the processing of any remaining rows instead of telling SQL Server to cancel all further query processing for this Command.
Using the DataAdapter Fill Method to Process the RETURN Value and OUTPUT Parameters
The following code constructs a DataAdapter with its associated SelectCommand to execute a multiple-resultset stored procedure that returns several OUTPUT parameters. Note that the parameters must be named correctly when working with SQL Server. That is they match the names used in the stored procedure. Order is not important, but you must include any parameter that does not have a default value set in the stored procedure.
Listing 4. Constructing a Command and using Fill to execute the query
Once the Fill method executes the query and processes the rowsets, the RETURN value and OUTPUT parameters are available through the SelectCommand.Parameters collection either by ordinal position or by name as shown in listing 5.
Listing 5. Displaying the values of the Return Value and OUTPUT parameters
Using a DataReader to Process the RETURN Value and OUTPUT Parameters
This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery if you don't have a rowset to process), but there are several other steps you'll need to take to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and a loop to process the rowsets, and then capture the Return value and OUTPUT parameters. You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.
Listing 6. Displaying the values of the Return Value and OUTPUT parameters
This fairly brief article walked you through the details of handling resultsets and the rowsets and other stuff they contain. You shouldn't have trouble getting to the gazoutas after this—at least I hope not.
About the Author
William (Bill) Vaughn is president of Beta V Corporation based in Redmond, Washington. He provides training, mentoring, and consulting services to clients around the globe, specializing in Visual Basic and SQL Server data access architecture and best practices. William's latest books are ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers—2nd Edition and the C# version ADO.NET Examples and Best Practices for C# Programmers. Both are available from Apress. William is also the author of many articles and training courses and is a top-rated speaker at computer conferences worldwide. You can reach him at firstname.lastname@example.org.