Retrieving the Gazoutas: Understanding SQL Server Return Codes and Output Parameters

William Vaughn
Beta V Corporation

April 2003

Applies to:
   Microsoft ADO.NET
   Microsoft SQL Server™

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)

Download the GazoutasSetup.msi sample file.

Contents

How Are Queries Processed?
Capturing the Gazoutas
Handling More Complex Queries
Managing Return Value and OUTPUT Parameters
Using the DataAdapter Fill Method to Process the RETURN Value and OUTPUT Parameters
Using a DataReader to Process the RETURN Value and OUTPUT Parameters
Conclusion

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.

Processing Resultsets

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:

   intRowsReturned = MyDs.Tables(0).Rows.Count

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

  ' Six result sets -- with four rowsets, 
  ' one empty rowset, two action     queries
Dim sql As String() = {"SELECT TOP 5 ContactName, City ", _
   "   FROM Customers WHERE Fax IS NULL", _
   " SELECT Top 5 OrderID, UnitPrice ", _
   "   FROM [Order Details] ORDER BY UnitPrice desc ", _
   " SELECT TOP 5 OrderID, UnitPrice ", _
   "   FROM [Order Details] WHERE 1=0 ", _
   " UPDATE Region SET RegionID =  -1", _ 
   "   WHERE RegionDescription = 'Unknown' ", _
   " DELETE Region WHERE RegionID = -1  ", _
   " SELECT TOP 5 LastName, FirstName, Region FROM Employees"}

Dim strT As String, sbSQL As New System.Text.StringBuilder()
For Each strT In sql
  sbSQL.Append(strT)
Next

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

Dim cmd As New SqlCommand(sbSQL.ToString, cn)
Dim dr As SqlDataReader
cn.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
bolRows = dr.Read   ' Read first row of rowset
Do
    If bolRows Then ProcessRows(dr) ' Dump DataReader rows to ListBox
    bolMoreResults = dr.NextResult
    If bolMoreResults Then
      bolRows = dr.Read()
    End If
Loop While bolMoreResults

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

Sub ProcessRows(ByVal dr As SqlDataReader)
Try
 ListBox1.Items.Add("------> RecordsAffected:"  _
  & dr.RecordsAffected.ToString)
 If dr.RecordsAffected >= 0 Then  
    ' Value is batch-wide total rows affected,
    '  but it's -1 if batch is running a SELECT.
    ' Changes state from -1 to N once first action query is executed
    ListBox1.Items.Add("---> Action query executed") 
    ' But the next rowset is queued up
 End If
 Do   '  process result set
   ListBox1.Items.Add(dr.GetValue(0).ToString & " --  _
   " & dr.GetValue(1).ToString)
 Loop While (dr.Read)
Catch ex As Exception
  MsgBox("Problem reading rows..." & ex.ToString)
End Try
End Sub

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

Dim bolEOF As Boolean
cn = New SqlConnection("server=demoserver;" & _
    "database=Pubs;integrated security=sspi")
CreateTestProcedure()         ' Create test SP in Pubs DB
da = New SqlDataAdapter("OutputTitleInfoByTID", cn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
With da.SelectCommand.Parameters
    .Add("@TID", SqlDbType.VarChar, 6).Value = "BU1032"
    .Add("@Title", SqlDbType.VarChar, 80).Direction _
        = ParameterDirection.Output
    .Add("@Type", SqlDbType.VarChar, 40).Direction _
        = ParameterDirection.Output
    .Add("@Price", SqlDbType.Money).Direction _
        = ParameterDirection.Output
    .Add("@Advance", SqlDbType.Money).Direction _
        = ParameterDirection.Output
    .Add("@ReturnValue", SqlDbType.Int).Direction _
        = ParameterDirection.ReturnValue
    ds = New DataSet
    da.Fill(ds)

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

Debug.WriteLine("@ReturnValue:" & _
    .Item("@ReturnValue").Value.ToString)
Debug.WriteLine("@Title:" & _
    .Item("@Title").Value.ToString)
Debug.WriteLine("@Type:" & _
    .Item("@Type").Value.ToString)
Debug.WriteLine("@Advance:" & _
    .Item("@Advance").Value.ToString)
Debug.WriteLine("@Price:" & _
    .Item("@Price").Value.ToString)
Debug.WriteLine("@Advance:" & _
    .Item("@Advance").Value.ToString)

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

With cmd.Parameters
    cn.Open()
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    ' Process rowset(s)
    bolEOF = dr.Read
    Do
        Do While bolEOF = True
            ' Process rows
            bolEOF = dr.Read()
        Loop
    Loop While dr.NextResult = True
    cmd.Cancel()
    dr.Close()
    Debug.WriteLine("@ReturnValue:" & _
        .Item("@ReturnValue").Value.ToString)
    Debug.WriteLine("@Title:" & _
        .Item("@Title").Value.ToString)
    Debug.WriteLine("@Type:" & _
        .Item("@Type").Value.ToString)
    Debug.WriteLine("@Advance:" & _
        .Item("@Advance").Value.ToString)
    Debug.WriteLine("@Price:" & _
        .Item("@Price").Value.ToString)
End With

Conclusion

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 billva@nwlink.com.