Working with Stored Procedures | Ado Examples and Best Practices

Team-Fly

I've folded some discussion of stored procedure handling into the preceding discussions, so you should have a pretty good idea of how stored procedures are executed. This section summarizes those points and adds a few new ones that I haven't yet touched on.

First, ADO was designed from its inception to execute stored procedures—at least simple ones. While there are a few disconnects here and there, ADO does a great job of handling stored procedure invocation and the stuff that stored procedures return, including the return status, output parameters, and both single and multiple result sets, along with their rowsets.

You don't have to do much to get ADO to invoke your stored procedure. You no longer have to hard-code the call statement as you did in RDO, and for simple stored procedures, you can simply reference them as methods of the Connection object. I'll show you how to construct a call statement for those situations where either you want to know what's going on behind the scenes or you want to get closer to the inner workings of ADO.

When you want to capture more than just the rowsets from a stored procedure, such as the return status or OUTPUT parameters, you must construct a Command object to handle them for you. I've discussed how to construct a Command object before, so I won't discuss it again. Just keep these tips in mind:

Set the CommandText to the name (just the name) of the stored procedure. Yes, there are a couple of other options, which I'll discuss in a minute.
Set the Name property to the stored procedure's name (optional). The CommandText and Name properties should match.
Set the Command type to adCmdStoredProc to tell ADO the CommandText contains the name of a stored procedure.
Set the ActiveConnection to the target server capable of executing the stored procedure.
Fill in the Parameters collection. As was discussed in Chapter 5, ADO can do this by using the Refresh method or by hand using the Append and CreateParameter methods.
Set the correct direction for each noninput Parameter, regardless of how the Parameters collection is populated. This ensures that ADO understands how and when to handle the parameter.
Set the Value property to assign a default setting for each parameter.

Executing Stored Procedures

After you've set up a Command object, executing a stored procedure is just a process of setting the execution-time parameter values and executing the Command object. Simple? Well, actually, there are other, even simpler ways to execute stored procedures—even those stored procedures that require input arguments. Sure, you have to use a Command object if you expect to capture Return Status or OUTPUT parameters; but if that's not the case, you can get away with a lot less. In any case, you should be familiar with the techniques to execute Command objects.

What you have to be careful about is the impact of your choice of ADO syntax. As I said, ADO was designed from the outset to execute stored procedures. Because of this, and because the ADO team wanted to support legacy code from RDO and ADO, a number of antiquated techniques will work.

Performance Considerations

Before we look at the examples, note that the execution times for all of these techniques were almost identical—after the second execution. That is, the first time these commands were executed, ADO took about 0.5 seconds (half a second) to run on my computer.^[4] The second time took about half of that time (0.28 seconds), but all subsequent executions took (roughly) between 0.038 and 0.007 seconds to run. Apparently, there is some setup time that is not necessary when the query is reexecuted. I suspect the data and query plan being cached on the server is part of this difference, as well as connection-pooling overhead.

When the system is left idle for a while or the server is restarted, these cached structures are flushed and the timings return to their initial state. Other techniques (Open method vs. stored procedure as method) seem to require initial setup as well, but the server-side of the equation seemed to help any query that executed the same stored procedure.

Let's look at example code for the top four techniques: stored procedure as Connection method, Recordset Open method, Connection Execute method, and Command Execute method. Yes, there are other methods, but they don't really buy you anything except a reputation for obscure code.

Using the Stored Procedure as a Connection Method

The technique in this first example seems to be missing from most documentation. It's the simplest of all and seems to work more elegantly and efficiently than most, if not all, other techniques. It takes advantage of the fact that ADO exposes all stored procedures as methods of the Command object. This means you can simply code the stored procedure as shown in the following code, followed by the input arguments (no, you can't specify return status or OUTPUT arguments), followed by the preinitialized Recordset to receive the first rowset. ADO does not construct a Command object, but it does set the Source property to the appropriate ODBC Call statement, just as we would do in RDO.

 cn.Au42 1950, 1960, rs

When this code executes, the SQL Server 7.0 profiler simply shows the stored procedure being executed with its input parameters. There are no "preparation" steps, no use of sp_executesql, no nothing—just the stored procedure call. Cool.

 Profiler: Au42 1950, 1960

Using the Recordset Open Method

This approach passes a string containing the stored procedure, along with the parameters, to the Recordset Open method.

 strSource = "au42 " & txtParm1.Text & ", " & txtParm2.Text

In some tests, however, I found ADO (sometimes) passed the arguments as quoted strings to the server. For example, sometimes ADO sent this to the server to execute:

 Profiler: Au42 '1950', '1960'

This did not seem to bother the server, but if it does, you can cast the Text property to an integer using the CInt function:

 strSource = "au42 " & CInt(txtParm1.Text) & ", " & CInt(txtParm2.Text) rs.Open strSource, cn, Options:=adCmdText

This approach enables you to set up the Recordset ahead of time, so you can choose the type of cursor and locking desired, along with other appropriate Recordset properties.

Using the Connection Execute Method

We discussed the Connection Execute method in Chapter 4, so you should be familiar with its use. Remember that you can get the RecordsAffected value back from action queries, but not from SELECT queries. However, you also have to settle for the default firehose (RO/FO/CacheSize=1) cursor, because the Execute method manufactures the Recordset for you.

We pass in the same source string as constructed in the previous example.

 Set rs = cn.Execute(strSource, intRA)

Using the Command Execute Method

We discussed using the Command object in Chapter 5. Executing a stored procedure with the Command object is no different than the other techniques, in respect to what gets executed on the server. In this case, however, you have to provide the parameters either by using a Variant array containing the parameters, or by passing in the parameters using direct references to the Command object. Both techniques are shown below.

Providing the parameters in a Variant array:

 Set rs = cmd.Execute(intRA, varParams)

Setting each parameter individually:

 cmd.Parameters(1) = txtParm1.Text cmd.Parameters(2) = txtParm2.Text Set rs = cmd.Execute(intRA)

Dealing with Stored Procedure Result Sets

The tricky part, which is the handling of the stuff that returns (but it isn't that tricky), comes after the stored procedure is executed. Remember that data providers return one or more result sets from the data provider, whether or not you're executing a stored procedure. An individual result set contains:

No or one Rowset: Stored procedures return no rowset to any number of rowsets. These must be captured using ADO syntax that specifies a Recordset, unless you simply want to discard the results.
A rows affected value: When you execute an action query and don't use theSET NOCOUNT ON, the provider returns the number of rows inserted, deleted, or updated.
A row count: After the provider completes processing the query, it returns a tally of the rows processed. If the cursor supports it, this value is assigned to the RecordCount property once it arrives.
A Return Status integer: All stored procedures send back a return status, but unless you or ADO constructed the Parameters collection to capture this integer, it falls in the bit bucket.
OUTPUT parameters: Stored procedures can and should return OUTPUT parameters whenever possible—especially when it can eliminate one or more rowsets.

When you examine the Recordset ADO returns after executing your stored procedure, but before you start wondering where the rows are, consider the nature of a stored procedure. Stored procedures are programs—plain and simple. These programs can return no rowsets at all, and perhaps not even return status or output parameters. On the other hand, they can return any number of rowsets, rows-affected values, and output parameters (within the limitations of the data provider). And as I said, the set of rowsets, rows-affected values, and output parameters is called a result set. So it's easier to simply say stored procedures can return from one to any number of result sets.

To effectively use stored procedures, you must understand what they do (duhh). Some stored procedures return a different combination of rowsets and rows-affected values based on the logic, and thus a different number of result sets. Suppose you have a procedure that runs once a month to generate sales totals. The procedure runs one section of code on an "ordinary" month, but additional sections of code run at the end of the quarter, midyear, and at the end of the year. Because of this, your application needs to be prepared forn result sets.

Note

INSERT, UPDATE, and DELETE statements all return rows-affected values. ADO does not make it easy to fetch these unless you execute a stored procedure using the Execute method, and then only if there is one statement. It does not work for batches.

Okay, so you ran the query and examined the Recordset. You discovered that the Recordset object is not Nothing^[5] (it's instantiated), but it's also not open (State = adStateClosed). What's the deal? Well, it could be that your first result set did not contain a rowset. For example, if the first statement in the stored procedure is an UPDATE statement (or any action operation), a result set is generated and ADO is informed that the first data is available. ADO subsequently constructs a Recordset to hold the result set and discovers there is no rowset, so it cannot open the Recordset. However, if your stored procedure executes a SELECT statement that returns no rows, an empty rowset is generated and ADO returns the instantiated Recordset with EOF=True.

A technique you can use to reduce overhead in complex stored procedures is to ignore the rows-affected value. That is, you can use the SET NOCOUNT ON TSQL statement in the stored procedure to tell SQL Server to not bother generating result sets just for the rows-affected values. Doing so saves time and can improve the execution speed of your code—it's another best practice.

As a point of reference, ADO 1.5 executed SET NOCOUNT ON automatically. This behavior was changed in ADO 2.0, which means that if you upgrade to ADO 2.0 or later (on purpose or not), your stored procedure handlers will now receive extra action query result sets—and will probably break. A fix for this problem is to add the SET NOCOUNT ON command to the stored procedures in question.

Warning

If you use the SET NOCOUNT ON TSQL statement outside the scope of a stored procedure, the setting is persisted in the connection. This means all subsequent use of the connection uses this option—even those clients inheriting the connection from the connection pool. Be sure to turn this option off, by using SET NOCOUNT OFF, when you're done.

Capturing RecordsAffected Values

When your result set executes an action query, it returns a rows-affected value—unless you disable this functionality using SET NOCOUNT ON. For example, if you execute an Update statement that affects all rows in the Animals table where Type = "pig", the rows-affected value should return 70 if there are 70 qualifying pigs in the table.

The rows-affected value can be captured using two methods—the Connection.Execute method or the NextRecordset method. Both of these ADO methods support returned arguments to capture the rows-affected value. If you don't turn off the rows-affected values using SET NOCOUNT ON when executing a stored procedure that has several action queries, ADO can expose each action query's rows-affected value using the NextRecordset method.

 Set rs = rs.NextRecordset(intRA)

Accessing OUTPUT Parameters

One of the most important components of a result set can be the set of OUTPUT parameters passed back from stored procedures. OUTPUT parameters can be used instead of rowsets for singleton operations—operations that return only a single set of values. More importantly, OUTPUT parameters can be returned by stored procedures insteadof a rowset, thus avoiding the (considerable) expense of constructing a Recordset. The OUTPUT parameter can be set up to return any provider datatype, from integers to BLOBs. You can also return computed or aggregate information in OUTPUT parameters drawn from complex multitable queries. While your data provider limits the number of OUTPUT parameters (SQL Server 7.0 limits stored procedure parameters to 1,024), you can still send a large number of values back to the ADO client.

OUTPUT parameters are easy to manage in ADO. While ADO itself stumbles a bit when constructing a Parameter object to deal with individual OUTPUT parameters, you can easily overcome this difugelty. Basically, the problem lies in the fact that the data providers don't differentiate between OUTPUT and INPUT/OUTPUT parameters, so ADO assumes that all OUTPUT parameters are INPUT/OUTPUT. All you have to do is change the Direction property to adParamOutput before executing the query.

Note

If you return a rowset and OUTPUT parameters or a Return status integer, you have to close the Recordset before they are visible. I discuss this in detail a little later in this chapter.

Let's walk through an example of a routine that calls a stored procedure that only returns OUTPUT (and return status) parameters, but no rowset. This meanswe won't need a Recordset at all, and the OUTPUT parameters will be available as soon as the query is executed.

 Create PROCEDURE OutputTitleByISBN     (@ISBNWanted varchar(20) = '0-0233058-8-6',     @Title VarChar(250) OUTPUT,     @Author VarChar(50) output,     @TypeDescription varchar(30) output,     @Price money output) AS Select @Title = Title,     @Author = Author,     @Price = Price,     @TypeDescription = v.Description from Titles t, Title_Author ta, Authors a, ValidTitleTypes v Where t.ISBN = @ISBNWanted and a.au_id = ta.au_id and ta.isbn=ta.isbn and v.type = t.type

The Visual Basic code that executes the preceding stored procedure is fairly straightforward, as shown next. The routine for constructing the Command object is in the Form_Load event, where it is executed but once.

 With cmd     .CommandText = "OutputTitleByISBN"     .Name = "GetTitle"     .CommandType = adCmdStoredProc     .Parameters.Append     .CreateParameter("ReturnStatus", adInteger, adParamReturnValue)     .Parameters.Append .CreateParameter("ISBNWanted", adVarChar, adParamInput, 20)     .Parameters.Append .CreateParameter("Title", adVarChar, adParamOutput, 250)     .Parameters.Append .CreateParameter("Author", adVarChar, adParamOutput, 50)     .Parameters.Append .CreateParameter("Type", adVarChar, adParamOutput, 30)     .Parameters.Append .CreateParameter("Price", adCurrency, adParamOutput, 200)     .ActiveConnection = cn End With End Sub

The following routine parses the returning OUTPUT parameters and paints them on the form. Notice that the Command object is executed using the adExecuteNoRecords option to prevent the construction of a Recordset. This helps performance.

 Private Sub cmbISBNs_Click() Dim parP As Parameter Dim intRa As Integer If cmd.ActiveConnection Is Nothing Then Exit Sub picPrint.Cls With cmd    .Parameters(1) = cmbISBNs.Text    .Execute intRa, , adExecuteNoRecords    picPrint.Print "Records affected:"; intRa    For Each parP In cmd.Parameters        picPrint.Print parP.Name, Tab(15), parP.Value Next End With End Sub

Managing Multiple Result Sets

One question that comes up often (I've raised it myself) is, "When are the OUTPUT parameters available to the client if I combine OUTPUT parameters with rowsets?" Well, that's an interesting question. It only comes up in cases where the stored procedure returns both output parameters and a rowset that's transmogrified into a Recordset. A very interesting point came up when I tried this with a dual-purpose stored procedure—I discovered that if you touch the Parameters collection before closing the Recordset, all values are lost. That is, once the query is executed, you must fetch the rowset from the Recordset prior to even attempting to view the Parameters collection (as in the Locals window). If you don't fetch the Recordset first and close it before you peek at the Parameters, they are set to empty. Isn't that special?

The following example parses the rowset and the OUTPUT parameters returned from a dual-mode (OUTPUT and rowset) query. Notice how the Recordset object must be closed (but not necessarily set to Nothing) before attempting to access the Parameters collection. The OUTPUT parameters are sent in the final packet returned to the client once the query is complete.

 Dim parP As Parameter Dim intRa As Integer If cmd.ActiveConnection Is Nothing Then Exit Sub picPrint.Cls Set rs = New Recordset With cmd     .Parameters(1) = cmbISBNs.Text     Set rs = .Execute(intRa)     Set MSHFlexGrid1.Recordset = rs     rs.Close     picPrint.Print "Records affected:"; intRa     For Each parP In cmd.Parameters         picPrint.Print parP.Name, Tab(15), parP.Value     Next End With End Sub

Note

When you describe the parameters for any stored procedure, remember that Visual Basic and ADO have no clue how to tie the names of your parameters to the parameter names in the stored procedure. ADO expects you to define these positionally, starting (optionally) with the ReturnStatus parameter, in the order in which they are defined in the stored procedure. That is unless you install ADO 2.6. This new version supports named parameters for the first time.

The following code walks through each of the returned Recordsets. It uses the NextRecordset method to flush the current Recordset and enable the next. This is repeated until the retrieved Recordset is set to Nothing.

 … Process first Recordset Do    Set rs = rs.NextRecordset    … Process next Result set Loop Until rs is Nothing

The NextRecordset method closes the existing Recordset and repeats the process of Recordset construction. At this point ADO either replaces the existing Recordset with the next result set or returns a Recordset set to Nothing. Remember that subsequent Recordset objects might (just might) contain rowsets, so be prepared for instantiated, but closed, Recordset objects when there is no rowset—as when an action query is in the script or stored procedure. A closed state (adStateClosed) Recordset is returned when there is another result set, but it contains no rowset—as when you execute an action query.

Tip

The NextRecordset method does not work correctly if you declare the Recordset object using the Dim xx As New Recordset syntax.

Apparently, ADO handles multiple result sets differently than earlier data access interfaces. In the past, the server did not process each subsequent result set until the client dealt with the preceding result set. That is, unless you canceled the query or canceled the whole operation (both are options in DB-Library and RDO), the server would not continue to process the stored procedure once the cancel command was received. However, despite what the MSDN doc says, the spec says that the provider might choose to process all result sets in the stored procedure as a unit, one after another, sending the individual result sets back to the client one-at-a-time. This seems to be what ADO is doing. Because of this, you cannot expect the Cancel method to have much effect on the processing of a stored procedure—even on one that returns multiple result sets.

The NextRecordset syntax shown in the previous code example overlays an existing Recordset object with the next result set. While you can assign the next Recordset to another Recordset variable, you won't be able to reference the previous Recordset once NextRecordset has executed. However, there's nothing to stop you from persisting each Recordset to a Stream as it arrives. This way you could manipulate each one at your leisure.

Note

The Command object's InfoMessage fires a "No more results" message when all Recordsets have been processed. This is another way to determine when all Recordsets have been visited.

To illustrate the use of multiple result set stored procedures, I wrote an example application (on the CD, see ..\sample applications\Recordset object\ stored procedure\multiple result sets). The stored procedure parser in this application is pretty typical of a simple multiple result set handler, but before I show you this code, take a look at the code used to populate the two drop-down ListBox controls that contain the valid types and valid ISBNs used in the program. This code calls the stored procedure that returns two separate rowsets—one for each drop-down. The parser simply walks through the rows and populates the lists from the rowsets. The stored procedure looks like this:

 CREATE Procedure GetValidTypesAndISBNs     (@ISBNRange varchar(20) = 'H%' , @MaxRows integer = 50) As     Select Type, Description from ValidTitleTypes Set Rowcount @MaxRows     Select ISBN from Titles where Title like @ISBNRange Set RowCount 0     return @@Rowcount

The code in Visual Basic that runs and parses this stored procedure is also pretty simple:

 Set rs = New Recordset cn.GetValidTypesAndISBNs "H%", 50, rs Do Until rs.EOF     cmbValidTypes.AddItem rs(0) & " : " & rs(1)     rs.MoveNext Loop cmbValidTypes.ListIndex = 0 Set rs = rs.NextRecordset Do Until rs.EOF     cmbValidISBNs.AddItem rs(0)     rs.MoveNext Loop cmbValidISBNs.ListIndex = 0 rs.Close

The routine used in the run procedure is a little more complex, but it covers most of the bases when accessing multiple result set stored procedures. In this case, we test the Recordset State property for adStateOpen to see if the result set has a rowset, and the EOF property to see whether there are rows in the rowset. If there are rows, we display them in the grid. If not, we simply step to the next Recordset using the NextRecordset method until this method returns a Recordset set to Nothing. I laid out this logic in a flowchart (takes me back to my IBM 360 days) that should make this process clearer. The flowchart is shown in Figure 7-6, and the code looks like this:

 Do    If rs.State = adStateOpen Then        If rs.EOF Then Else Set MSHFlexGrid1.Recordset = rs    'No rows?    End If    Set rs = rs.NextRecordset    If rs Is Nothing Then    Else       i = MsgBox("Next recordset? Press Cancel to end.", _       vbOKCancel + vbInformation)       If i = vbCancel Then Exit Do       MSHFlexGrid1.Clear    End If Loop Until rs Is Nothing

Figure 7-6: Handling multiple result sets returned from stored procedures

Handling Complex Result Sets

Stored procedures return rowsets, return status values, and OUTPUT parameters, and they can also return:

Extra result sets, including rowsets generated by COMPUTE statements
Strings generated by TSQL PRINT statements
Values and "error" messages returned by RAISERROR statements

Remember that the RAISERROR function in TSQL does not necessarily indicate a fatal error. For years, RAISERROR has been used to signal logical conditions that need extra attention at the client. For example, when an application sells a pair of green high-heel shoes and calls a stored procedure to record the sale, an UPDATE statement is executed that affects the quantity-on-hand value for that shoe type. The stored procedure might also check to see whether the current quantity-on-hand is lower than a specified reorder trigger amount. If that's the case, the stored procedure could use a RAISERROR function to signal that the update succeeded, but the quantity of green high-heel shoes is low. It's the responsibility of your application to deal with this extra chunk of information passed back to your client or component.

Handling Compute Statements

The TSQL COMPUTE statement generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query. ADO returns all of this extra data as additional Recordsets.

Ideally, COMPUTE statements are handled in hierarchical Recordset structures. While this example does not do that, it is possible to set this up using ADO. I discuss hierarchical techniques in Chapter 10.

The following stored procedure is an example of COMPUTE and COMPUTE BY TSQL statements. These provide summary information on grouped rowsets in the form of additional aggregated totals and subtotals. Each time a group is segmented by the stored procedure, an additional rowset is generated for the COMPUTE totals. I show you how to process this stored procedure next. This code can also be found on the CD under \Sample Applications\Compute By.

 Create procedure ComputeAvgPriceByPublisherAndType     (@statewanted varchar(2) = 'WA',     @CompanyWanted varchar(20)) as SELECT substring(Company_Name,1,20)Company, type, price FROM titles t, publishers p WHERE t.pubid = p.pubid and state = 'wa' and Company_Name like @CompanyWanted + '%' and price > 0 order BY t.pubid,type COMPUTE avg(price) BY t.pubid, type COMPUTE avg(price) BY t.pubid COMPUTE avg(price)

There are three levels of COMPUTE BY result sets in this procedure, in addition to multiple sets of detail rows—one set for each type and publisher:

Average price by publisher and type
Average price by publisher for all types
Average price for all publishers and all types

The code that executes and processes the result sets returned by this stored procedure is fairly simple. We start by setting up a form to capture the state and company wanted. The Form_Load event handler creates a Command object and its parameter collection to manage the two input parameters.

 Private Sub Form_Load() Set cn = New Connection Set cmd = New Command cn.Open "dsn=localserver", "SA", "" With cmd     .CommandText = "ComputeAvgPriceByPublisherAndType"     .Name = "ComputeTest"     .CommandType = adCmdStoredProc     .Parameters.Append_     .CreateParameter("StateWanted", adVarChar, adParamInput, 2, "ca")     .Parameters.Append_     .CreateParameter("CompanyWanted", adVarChar, adParamInput, 20)     .ActiveConnection = cn End With

The cmdRun button click event, shown next, executes the Command object, passing in the arguments from the TextBox controls. The initial Recordset contains the first set of detail rows from the query. This Recordset is applied to the MSHFlexGrid control to be displayed.

 Private Sub cmdRun_Click() Set rs = New Recordset cn.ComputeTest txtStateWanted.Text, txtCompany.Text, rs Set MSHFlexGrid1.Recordset = rs

To fetch the next set of detail rows or COMPUTE aggregate, we simply use the NextRecordset method to fetch the next Recordset in the stream of data being returned from the server. When no further result sets are available, the NextRecordset method returns Nothing.

 End Sub Private Sub cmdNext_Click() Set rs = rs.NextRecordset If rs Is Nothing Then     MsgBox "No more results..."     Set MSHFlexGrid1.Recordset = Nothing Else     Set MSHFlexGrid1.Recordset = rs End If End Sub

Handling PRINT and RAISERROR Statements

ADO handles the PRINT and RAISERROR TSQL statements similarly. Both ire the InfoMessage event, which returns the message text specified in the TSQL statement. This text string is returned in the ADODB.Error object's Description property.

 Private Sub cnn_InfoMessage(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) Debug.Print "A message arrived: ",_ pError.Description, pError.NativeError, pError.Number End Sub

However, there are a few caveats:

RAISERROR statements in SQL Server must have a severity level of 11 to 18. Lower severity errors are ignored. Unfortunately, a single RAISERROR message terminates the query being executed, so subsequent result sets are tossed. (Sigh.)
PRINT statements in SQL Server can also populate the ADO Errors collection. However, PRINT statements are severity level 0, so at least one RAISERROR statement greater than severity 10 is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection. (Double sigh.)
RETURN status values in a stored procedure must be associated with at least one result set. (Triple sigh.)

All this means that although you can process RAISERROR and PRINT statements, many (many) will be ignored unless your stored procedures are revised to conform to these restrictions. This implementation is a giant step backward for ADO when compared to DB-Library, which supported far more functionality with RAISERROR.

If you don't enable the Connection object's InfoMessage event, the PRINT and RAISERROR messages and error number values are lost, and your application proceeds as if nothing happened. That is, unless the RAISERROR severity number is high enough to tell ADO that something serious has occurred while executing the stored procedure. This is because ADO returns only severities 11 to 18 as "informational" events. PRINT statements are treated as severity 0 errors.

Note

To retrieve a RETURN value in ADO with a stored procedure, there must be at least one result set. To work around this problem, when no result sets are specified (in the ADO sample code), the stored procedure executes a SELECT NULL to return a null result set to ADO, thereby populating the RETURN value. In addition, to working around the issue of specifying no RAISERROR statements and a combination of PRINT statements, default RAISERROR statements are generated in order to provide a context for returning the PRINT statement via ADO.

The RAISERROR and Messages Dilemma

I also discovered that there's a dramatic difference in the way that the OLE DB provider for ODBC and the native OLE DB provider for SQL Server manage RAISERROR and PRINT statements. The ODBC provider seems to return all messages generated by the server, including simple informational messages. However, it does not return any RAISERROR messages. In my test, only the "No more results" message was returned when I stepped past the last result set after having executed a multiple result set stored procedure. In contrast, the OLE DB native provider returns very few messages, but it does return the first RAISERROR message, although this terminates the query. Both providers simply ignore any RAISERROR message with a severity less than 11.

I queried the SQL Server internal support alias on campus and got a detailed explanation of what's going on. While I don't completely agree with the implementation, I can see why it has evolved into what we're seeing. According to the gurus, handling the messages and errors raised by SQL Server in OLE DB and ODBC-based environments has always been difficult because of two fundamental factors:

The way SQL Server returns errors and messages at the TDS level
The way OLE DB providers handle messages and errors

The first of these issues has to do with how SQL Server returns errors and messages. Because DB-Library was SQL Server's first low-level interface, much of the confusion has to do with mapping functionality that was available when DB-Library was fully supported. DB-Library expects applications to have separate error and message handlers, each exposing a callback function. We implemented this in Visual Basic from the earliest days using special API calls. These error and message callbacks fired events that we could trap to manage all errors and messages (regardless of severity).

Although this architecture was considered for ODBC 1.0, it was dropped in favor of the more generic ODBC mechanism, where the ODBC function returns codes that indicate the presence of errors or messages. These messages and errors could be subsequently retrieved using SQLGetDiagRec, but there was no event support for this. This means that the developer has to code an error-polling routine to regularly check to see whether an error (or message) has arrived.

To work with this approach, when the SQL Server ODBC driver detects items that would have gone to a DB-Library message handler, it returns SQL_SUCCESS_WITH_INFO to the ODBC function call. The ODBC driver only sets SQL_ERROR if it gets an item that would go to the DB-Library error handler. When this happens, items such as the output of a PRINT or RAISERROR statement with a low severity only generate a SUCCESS_WITH_INFO return. If the provider ignores these, the error or message is ignored and lost.

On the OLE DB side of the problem, the architecture attempts to further distance the data provider (SQL Server) from the application. Because the OLE DB spec says that a provider cannot return DB_S_ERRORSOCCURRED if there are only warnings (such as low-severity RAISERROR calls), when SQLOLEDB (the native SQL Server OLE DB provider) gets messages back (as opposed to errors), it exposes the messages as error records. However, OLE DB still has to return S_OK to the OLE DB consumer. SQLOLEDB consumers have to be coded to test for the presence of OLE DB error records containing messages, even when they get S_OK. Because this coding is in ADO itself, there's little that you as an ADO developer can do about capturing messages and low-severity errors.

A few more information points to clarify the situation:

The OLE DB Provider for ODBC does not always check for messages when a driver returns SQL_SUCCESS_WITH_INFO. If ADO is not always returning messages from the SQLOLEDB provider, ADO cannot always populate an error collection if a provider returns S_OK. This means ADO applications have difficulty getting any PRINT statements, and don't get any RAISERROR statements with a low severity, as many OLE DB applications using the OLE DB Provider for ODBC are layered over the SQL Server ODBC driver.

Note
Other statements, such as DBCC statements, SET SHOWPLAN, and SET STATISTICS, also use the same mechanism as PRINT and low-severity RAISERROR statements. These are also ignored.
The fact that the ODBC-based SQL Server utilities, such as Query Analyzer and OSQL, return the output of these statements shows that it can be done properly.

The problem facing the SQL Server provider developers keeps getting increasingly complex as they tinker with the provider clockworks. If they "fix" this problem, lots of applications might fail, because they would now get errors and messages for operations that were simply ignored in the past. The Microsoft developers are looking for better solutions.

Handling Automatic Parameter Population in Transactions

I noticed something interesting about using the Parameters collection's Refresh method when working with stored procedures. If Refresh is called within a transaction, the data provider locks some of the system tables. After a Refresh call, and while this transaction is still active, calling Refresh from another application hangs. This also hangs SQL Enterprise Manager if the server node is expanded. This behavior can be seen with ADO 1.5, ADO 2.0, and MSDASQL provider with SQL 6.5. However, it does not seem to affect SQLOLEDB provider with SQL Server 6.5/SQL Server 7.0 or MSDASQL provider with SQL Server 7.0.

The reason for this behavior is that ODBC (when implemented through the use of the MSDASQL provider) returns the metadata associated with the Parameters collection via the SQL Server TempDB database. If this process is wrapped in an uncommitted transaction, any other process that tries to insert a row into the TempDB sysobjects table (say, by trying to automatically populate another Parameters collection) hangs until the transaction is rolled back or committed.

Remember that this happens only when the Parameters collection is automatically populated from within a transaction. If the client application manually populates the Parameters collection, no round-trip is made to the server to get the parameter information, and no locks are implemented in the TempDB database. Another workaround is to use automatic population, but use it outside the scope of the transaction. In that case, executing the Command object within the transaction will not cause excessive locking in TempDB.

I'm a big fan of avoiding automatic population whenever possible, because the application will take a pretty noticeable performance hit while it makes the round-trip to the server to obtain the metadata associated with the parameters collection.

Providing Stored Procedure Parameters by Hand

Those of you who are migrating from RDO might be tempted to roll your own Call statements and fill in the parameters by hand. I'm afraid I don't see why one would still want to do this, but for those who want a clearer path when migrating from RDO, here it is.

Instead of setting the CommandType to adCmdStoredProc, try adCmdText. In this case, you have to pass either a correctly formatted Call statement or the name of the stored procedure concatenated to any parameters. This seems pretty silly to me, as it takes more code and more work on the part of the providers and server to decode what you're trying to execute. The Call statement is formatted in the same way as was done in RDO.

Your query's parameters can be provided using a variety of techniques, each of which has advantages and drawbacks that include (or don't include) the capability to leverage your procedures and scale your applications. Although using the Command object is the preferred best practice technique, you might find the need to call stored procedures using the Call statement or simply by rolling your own CommandText. You can pass parameters to stored procedures (or to ordinary ad hoc queries) in the following ways:

As hard-coded arguments in an SQL query string. In this case, ADO can't really help manage the parameters for you, and other people can't use the query either, even if they are performing the same search. The real disadvantage here is that to change the parameter you have to recode, recompile, retest, and redeploy your application.
```
 MySPFindTitleByState 'CA' 
```
Using a question mark (?) parameter placeholder in an SQL statement. In this case, you enable ADO's capability to manage the parameters for you. Simply setting a Command object's CommandText property to this query string gives enough information to ADO and the data provider to build a rough Parameters collection. Your code doesn't have to deal with embedded quotes in the parameters passed, or any framing quotes, because ADO and the data provider handle these for you.
```
 MySPFindTitlesByState ?" 
```
As the question mark (?) parameter placeholders in a stored procedure call that accepts Input, Output, or Return Status arguments. We have now reached the next (but not the highest) level of sophistication when building parameter queries. When you execute a stored procedure with this Call syntax, ADO manages the query and its parameters for you. Because ADO is handling the parameters for you, you don't need to worry about embedded or framing quotes. The following quoted string is assigned to the CommandText property.
```
 "{Call MySPFindTitlesByState(?)}"  
```
As the question mark (?) parameter placeholders in a DataEnvironment object. You can also build parameter queries using the Data Environment Designer. The Data Environment Designer performs most of the complex tasks that you would have to duplicate in code and also provides a shareable way to build the fairly complex SQL statements required by parameter queries. That is, after you create a DataEnvironment object that references a stored procedure (or other query), you can share it among the other developers who also need access to this query. This approach also assumes you haven't read Chapter 12 on the Visual Database Tools. In that chapter, I detail the plethora of problems, oversights, and bugs you get when using the Data Environment Designer.

How to Use Parameter Markers

Each query parameter that you want to have ADO manage must be indicated by a question mark (?) in the text of the SQL statement. To execute a query that takes a single input parameter, your SQL statement would look something like this:

 Select Publisher from Publishers Where State = ?

Multiple parameters can be salted throughout the query, as required. You need to follow a few rules, though, and I get to those in a minute. But you can pass parameters to several queries at once when building multiple result set queries. For example, you could provide the following SQL statement for execution and expect it to manage all three parameters:

 Select Title from Titles Where Description between ? and ? Select Author from Authors where Author Like ?

Here are those multiple parameter tips and rules I promised:

Parameter queries can use question marks as placeholders for both the input and output parameters.
Stored procedure calls are surrounded by braces ({}), as shown in the following code. This string is applied to the CommandText property. Failure to use correct call syntax might not prevent the procedure from being executed, but the ADO might not be able to identify the parameter positions or markers.^[6]
```
 { ? = Call MySP } 
```
Passing Nulls: Many stored procedures require or permit you to pass NULL as an input Parameter. When you do, the stored procedure can substitute its own default value or take other logical action based on an unknown value being passed. There's no real mystery to passing a NULL—simply assign the Parameter Value property with the vbNull constant.

ODBC Call Syntax

Table 7-1 summarizes the ADO^[7] Call syntax that you have to include in your queries unless you use the UserConnection Designer, which creates this Call syntax for you. You need to include a question mark (?) for each parameter, regardless of direction.

Table 7-1: Handling parameters using the Call syntax
QUERY PARAMETER CONFIGURATION	ODBC CALL SYNTAX
No parameters	{Call My_sp}
All parameters marked—input, output, both	{Call My_sp (?, ?)}
Some parameters marked	{Call My_sp (?, 'Y')}
Query returns just a return status argument	{? = Call My_sp}
Query with the works	{? = Call My_sp (?, ?, 'Y')}

Acceptable Parameters

The types of data that are acceptable as parameters depends on the provider. For example, you can't always use a TEXT or IMAGE datatype as an output parameter—although the newer drivers are more tolerant of this option. In addition, if your query doesn't require parameters, or if it has no parameters in a specific invocation of the query, you can't use parentheses in the query. For example, for a stored procedure that does not require parameters, you can pass the following quoted string to the CommandText property.

 "{ ? = Call MySP }"

Thus, when executing a stored procedure that needs no input parameters, we still build a Parameters collection, but we use it to capture the return value parameter.

Here are the rules I mentioned earlier, having to do with what, when, and where parameters can be used—but only if you're rolling your own Call statements:

When you submit queries that return output parameters, these parameters must appear at the end of the list of your query's parameters.
While it is possible to provide both marked and unmarked (inline) parameters, your output parameters must still appear at the end of the list of parameters. This means that your stored procedure must be coded to place all output parameters at the end of the parameter list.
ADO versions 2.5 and earlier don't support named parameters for stored procedures. You can use named parameters when calling stored procedures using adCmdText, but ADO can't manage them for you. This is supported in ADO versions 2.6 and later.
For some (including the SQL Server) providers, all inline parameters must be placed to the right of marked parameters. If this isn't the case, ADO returns an error indicating "Wrong number of parameters." An inline parameter is one that you hard-code or provide yourself in lieu of using a parameter marker.
ADO supports BLOB data types as parameters, and you also can use the AppendChunk method against the Parameter object to pass TEXT or IMAGE datatypes as parameters into a procedure. When passing BLOBs in parameters, set the datatype to adLongVarChar.

Tip

Be sure to specifically address stored procedures that don't reside in the current (default) database.

Note

All stored procedures return a "return status" value, whether you ask for it or not. The value isn't exposed in the Parameters collection if you don't ask for it, but it's generated by the stored procedure nonetheless. I like to pass back some useful information from the stored procedure in this argument—for example, @@ROWCOUNT.

^[4]I also had the Profiler running, which would impact performance. See the sample application in …\Sample applications\Recordset Objects\Stored Procedures\Simple Example.

^[5]I hate double negatives, but there is no ADO or COM notation for Something. Wouldn't it be nice if we could say, "If RS is Something Then…." Wouldn't that be something?

^[6]Remember that ADO can generate the correct call syntax for you.

^[7]This "ADO" call syntax was originally developed for ODBC applications. It's used now for both ODBC and OLE DB interfaces.

Team-Fly