If you're running database-intensive code from Visual Basic or ASP, stored procedures can give you a great speed boost. With ADO 2.1, one of the components that makes up MDAC, this technique is easier to implement than ever.

Want to make it easier to use stored procedures in your application? Using some of the latest technologies in the Microsoft Data Access Components (MDAC), we're going to show you how to do just that. In addition, we are going to give you some highly reusable and powerful code that you can immediately plug and play with your Visual Basic® or ASP code. It all begins with ActiveX Data Objects (ADO), part of MDAC, and stored procedures.
      If you've been avoiding writing stored procedures because using them from your Visual Basic or Active Server Pages (ASP) code was cumbersome, you'll want to reevaluate that decision thanks to ADO 2.1. ADO makes using stored procedures easier than ever before. You can simply create a Command object, give it the name of the stored procedure, set a few parameters, execute it, and voilà! Now you can reap the benefits of stored procedures—speed, scalability, reusability, and encapsulation—by writing a few simple lines of code. In this article, we'll show you how to get even better mileage out of your stored procedures. We'll even provide some code to make using stored procedures more scalable and reusable.
      One of the main driving forces behind the surge of ADO's popularity is its integration with OLE DB. For years, ODBC was the best way to access your data sources. With OLE DB you can reach a wider range of data providers, have direct access to the data providers, greater data access speed, and greater control over how you manipulate the data in those data providers.
      These are obviously great advantages to using OLE DB. However, we'd be remiss if we did not mention some disadvantages. For instance, because OLE DB lets you access a data provider (such as SQL Server™) directly, your data access is limited by the features exposed by the data provider's OLE DB driver.

The Snag
      What does this have to do with using stored procedures from ADO? One of the features not exposed through the SQL Server 6.5 provider for OLE DB is the Refresh method of the ADO Parameters collection. Why should this concern you? Without the Refresh method, you have to manually create and add each parameter in code. Imagine having nine parameters in a stored procedure that updates data. You'd have to create and add each of these parameters in code and in the same sequence they appear in the stored procedure, or it just won't work! That's right, parameters are order-dependent.
      This simply wouldn't do. We did not want to police our stored procedures so that the parameters had to stay in the same order forever. However, we weren't ready to stray away from OLE DB without a fight. Using minimal code, we came up with a solid and effective way to refresh your parameters that still lets you use OLE DB and exploit all of its advantages. So brew a cup of coffee, sit back, and we'll walk you through the process.
      The Refresh method of the ADO Parameters collection does function properly when using the SQL Server 7.0 OLEDB provider. Therefore, it is not necessary to use our technique for SQL Server 7.0. (The technique discussed here is compatible with both SQL Server 6.5 and 7.0.) Also keep in mind that the procedures described here involve extra round trips to the server. For this reason, you may want to limit them to testing, not production, code.

The Scenario
      We'll use the Pubs database that comes set up with SQL Server to help create an interface that allows users to edit an author's information. All of this information is in the author table in Pubs. In the interest of speed, scalability, and reusability we'll create a stored procedure to change these values (see Figure 1). Then we can call this stored procedure from our interface, whether it be from a LAN-based or a Web-based client. This is a relatively common scenario for many developers.
      Notice that the stored procedure in Figure 1 (prUpdateAuthor) accepts nine parameters. The first eight fields can be changed by the user and the last parameter is the primary key of the table, so you know which record to update. This is a simple example of a commonly used stored procedure.
      Now let's take a look at the ADO code that executes this stored procedure. The code is shown in Figure 2. The code is quite a handful, since it has to create and add each parameter. Remember, the worst part is that you have to put the parameters in the same order as they appear in the stored procedure.
      It's preferable to use the Refresh method to fill your Parameters collection so you can refer to the parameters by name. That way, you can refer to them in any order you like. Also, with the Refresh method you do not have to worry about specifying the parameters' data types, sizes or directions. The code in Figure 3 shows you how to implement the Refresh method.
      Granted, the code in Figure 3 will not work unless you are using the SQL Server 7.0 provider. With SQL Server 6.5, the Refresh method will fail because it is not supported by the SQL Server 6.5 provider. Notice that this code is much more flexible, not to mention simple. If only it worked! So here is where we stepped in and wrote our own Refresh method.

Getting the Parameter List
      We had to use some of the system tables within SQL Server to write our own Refresh method. When we asked SQL Server for a stored procedure, we also queried it for the procedure's parameters, their names, data types, and so on. To do this, we created the stored procedure prStoredProcedureParameters, shown in Figure 4.
      prStoredProcedureParameters joins the syscolumns and sysobjects system tables to retrieve the information for the stored procedure that's passed in via the parameter @chvProcedureName. Before you can analyze what exactly this stored procedure returns, you need to understand what the ADO Parameters collection's Refresh method really does. The Refresh method determines all of the parameters' names, data types, and other characteristics. Included in the parameters is the stored procedure's return value. This is why we had to include the return value in prStoredProcedureParameters. All stored procedures have a return value, even if none is explicitly specified.
      We UNIONed the return value in our query because that information is not an actual parameter, so it will not appear in the syscolumns system table. The rest of the query looks intimidating, but it's actually quite simple when you break it down. We simply wanted the ordinal index of the parameter as it appeared within the stored procedure, so we included the colid field from the syscolumns table in the SELECT list. Then we needed to get the name of the parameter, so we included the name field from the syscolumns table in the SELECT list.
      Another issue that we mentioned earlier was the use of the Refresh method and compatibility with the SQL Server OLE DB provider (SQLOLEDB). If you want to use features such as Data Shaping, you need this provider, but it will not allow you to refresh parameters dynamically from the stored procedure in SQL Server. To resolve this issue, you can create a stored procedure that returns all the pertinent information about the parameters. Let's take a look at such a stored procedure.
      Some work was needed to determine the data type of the parameter. The data type is stored in the field usertype in the syscolumns table. However, the numeric value of this datatype means something totally different than the numeric value of the data types in ADO. So we had to translate the data types from the way SQL Server understands them to the way ADO understands them.
      For example, in SQL Server, 1, 2, and 19 represent the SQL Server data types CHAR, VARCHAR, and TEXT, respectively. In our stored procedure, we translated all three of these SQL Server data types to the ADO adVarChar data type, which is the numeric value 200. If this doesn't make sense to you, don't feel bad. We could never remember the translations either. That is why we put the comments to the right of each translation to explain how each data type translates to ADO.
      Finally, we wrapped up this stored procedure by retrieving the length, precision, scale, and direction. If we run this stored procedure and pass it the name of our prUpdateAuthor stored procedure, the output looks like Figure 5.
      As you can see, the stored procedure prStoredProcedureParameters returns all nine of the parameters plus the implicit return value "parameter" (which is always set to an index of 0). The best part about this code is that you can create this procedure once, then use it over and over again in your application (or in several applications).
      Now that we have explained how to retrieve this data from SQL Server, we can show you how to use this stored procedure from your Visual Basic or ASP application to make your code more flexible and powerful.

Rolling Your Own Refresh Method
      We will create the Refresh method using Visual Basic code. (This works in ASP with VBScript as well, with a just few alterations.) Our Refresh method (see Figure 6) calls the prStoredProcedureParameters stored procedure and fills our Parameters collection for us.
      Keep in mind that the code in Figure 6 is another subroutine that you just simply throw into your code library and never worry about again. All you have to do is pass it the ADO Command object for which you want to get the parameters. The only assumption that this code makes is that the Command object already has its ActiveConnection property set to a valid ADO Connection and that its CommandText property is set to a valid stored procedure name. If you do these two things, then the Refresh method will return a parameter-filled Command object that you can use by referring to its parameters by name in any order that you want.
      If you look through the code in our custom Refresh method, you will see that all we did was call the prStoredProcedureParameters stored procedure and pass it the name of the stored procedure whose parameter list we wanted to retrieve. Then we added the parameters to the Command object's Parameters collection using the ADO Parameters collection's Append method.

Finishing Touches
      The prStoredProcedureParameters stored procedure and the custom Refresh method are totally reusable and generic pieces of code, so you can call the Refresh method from your Visual Basic or ASP code:

 '— Indicate that we are using a stored procedure
 objCmd.CommandType = adCmdStoredProc
 '— Specify the name of the stored procedure
 objCmd.CommandText = " prUpdateAuthor"
 Set objCmd.ActiveConnection = objConn
 Refresh objCmd
 '— This equivocates to objCommand.Parameters.Refresh

http://msdn.microsoft.com/library/techart/msdn_visql.htm and http://msdn.microsoft.com/library/psdk/sql/adoprg01_20.htm

From the December 1999 of Microsoft Internet Developer.