Using Visual InterDev 1.0 to Pass Parameters to and from Microsoft SQL Server Stored Procedures

Mike Pope
Visual InterDev Technical Writer
Microsoft Corporation

August 1997

Editor's Note Information in this article applies to version 1.0 of Microsoft Visual InterDev.

Introduction

If your Microsoft® Visual InterDev™ application includes Active Server Pages (ASP) that interact with a Microsoft SQL Server™ database, and if that database contains stored procedures, you can use ActiveX™ Data Objects (ADO) to call the stored procedures. Calling stored procedures is a good idea because they are compiled and therefore generally run faster than SQL commands alone. In addition, you can call system stored procedures to manage your server or return system information.

Because stored procedures are designed to be used over and over, they generally rely on values passed to them, called input parameters. For example, a stored procedure might retrieve or update an employee record based on an employee ID passed to it and used in the WHERE clause of an SQL SELECT command or the SET clause of an UPDATE command.

Stored procedures can also return values. Most stored procedures are written to return an integer value, the return value, often used as an error flag. In addition, stored procedures can return information using an output parameter. Output parameters can return any type of data, except long data types such as text or image, and are used to return values calculated in the stored procedure. A typical example of an output parameter value might be a record count or an aggregate summary value.

This article explains how you can pass parameters to stored procedures and fetch return parameter values. Along the way, it offers advice on efficient or easy ways to accomplish these tasks, and tips for success.

Note All the script examples in this paper use Microsoft Visual Basic®, Scripting Edition (VBScript), but the principles apply to any language in which you can call ADO. Visual InterDev design-time controls (DTCs) generate script in the default language for the page.

Passing Input Parameters to a Stored Procedure

When you are working with a stored procedure that uses parameters, you most commonly pass one or more values to it. In Visual InterDev, you can set up a call to a stored procedure in several ways, depending on how comfortable you are with coding and how efficient your stored procedure call has to be:

Use the Data Command or DataRange Header DTCs to define the call to the stored procedure. This method is the easiest and results in efficient script.
Script the call yourself, and pass the parameters directly in the Execute method of an ADO Command object. If you are comfortable writing script, this is a relatively simple process, but is not the most efficient one.
Script the call yourself, and set up parameters in the ADO Command object's Parameters collection. This method is more verbose than simply passing parameters with the Execute method but is more efficient when it runs.

In most situations you'll probably just use the DTCs to create the database access script. However, you might find it useful to be familiar with all the ways of passing a parameter, either to be able to select a method suitable to your application or simply to understand what happens when you use a DTC.

Specifying Input Parameter Values in a DTC

To specify a stored procedure as the command for a DTC, insert the DTC in your page. Then open the Properties window for the DTC, choose the Control tab, and then choose Stored Procedure from the Command Type dialog box. When you do, the Command Text dialog box is loaded with the names of all the stored procedures accessible to the current data connection as shown in Figure 1.

Figure 1. The Control tab of the DTC Properties window

When you choose the name of a stored procedure, the DTC automatically determines whether the stored procedure requires parameters, and if so, what their data type and length is. Choose the Parameters tab in the Properties window, and you'll see a list of the parameters, as shown in Figure 2.

Figure 2. The Parameters tab of the DTC Properties window

Input parameters are marked with . The type and size information is read-only; you can specify only the value for each input parameter. To specify a value, select the parameter in the list, enter a value in the Set values for parameters box, and click the check mark. If you forget to click the check mark, the value won't be entered for that parameter.

You can enter any expression for the parameter value, including literals, the names of variables, or any combination that is a legal expression in ASP script. However, there are a few tricks:

To enter a literal value, just type it in. You probably wouldn't do this very often, but you might find it useful, for example, if you are calling a system stored procedure with a fixed value.
To enter a variable name or expression, enclose the value in square brackets and put an equal sign in front of it (for example, =[MyVar]). If you don't do this, the DTC will assume you are entering a literal value.
To enter an expression, put an equal sign in front of it.

The following table shows you examples of how to enter different types of expressions.

Table 1. Parameter Value Expressions

Value Type	Example	Enter this way
Literal	"Password" 12	`Password 12`
Variable	VLName	`=[vLName]`
Expression	Session("LoginID") Request.Form("LName")	`=Session("LoginID") =Request.Form("LName")`

If you are entering a variable that will contain text data (data types char or varchar in SQL Server), you can skip the equal sign in front of the variable in brackets. For example, you could enter [vLastName] instead of =[vLastName]. However, it's better to always include the equal sign for all variables.

Note In some versions of ADO, you might experience difficulties with the SQL Server Decimal data type. If so, you need to make a small change in the script generated by the DTC. For details, see "Fixing Decimal Data Types," later in this paper.

Figure 3 is what the Parameters tab of the DTC's Properties page might look like if you enter different types of parameters.

Figure 3. Possible parameter types

When you save the DTC, it generates script to call the stored procedure and pass parameters to it. If you're curious about this generated script, or if you want to manipulate the script further, see "Passing Input Parameters Using the Parameters Collection," later in this paper.

If you specified a variable for a parameter, you have to initialize it somewhere before the script generated by the DTC. The DTC script begins this way:

<!-- METADATA TYPE="DesignerControl" startspan

Above that section of script, add script to load the variable. For example, the following script gets the value of a Hypertext Markup Language (HTML) form control and puts it into a variable:

vLName = Request.Form("LastName")

In addition, of course, you have to provide a means to display (or otherwise use) the results of the stored procedure. If you use a Data Range Header DTC to generate the call for the stored procedure, you can use a Data Range Footer DTC to generate the script to display results. Alternatively, you can script the display by hand-editing the script generated by the DTC.

Passing Input Parameters in the Execute Method

If you prefer to write your own script to call the stored procedure, you can do so using just a few statements. If you are familiar with using ADO objects for data access, you'll see that passing parameters is like ordinary data access, with only minor additions.

For those not already familiar with ADO objects, here is a quick introduction. Data access is managed through three server-side ADO objects:

A Connection object that manages communication with the data source.
A Command object that manages the text and parameters of SQL commands and stored procedures.
A Recordset object that contains the result set (if one is created by the stored procedure).

In general, the script you write to call the stored procedure must:

Create a Connection object, and then open it, passing a connection string.
Create a Command object, and assign the connection object to it.
Set properties of the Command object to specify that you want to call a stored procedure (rather than run an SQL command directly), and to specify the name of the procedure.
Create and open the result set by calling the Command object's Execute method.

These four steps describe basic data access with ADO.

To pass a parameter to the stored procedure, you need just a couple of extra steps. First, before calling the Command object's Execute method, you call the Refresh method of the Command object's Parameters collection. Calling the Refresh method causes the Command object to read the information about the expected parameters and to be able to handle the data type and length of the data you will pass. If you forget this step, you'll see an error like this:

Unable to determine parameter type for at least one variant parameter.

Calling the Refresh method is an easy way to get data type information for the parameters, but requires an extra trip to the server to read the data type information, which is why this is not the most efficient way to pass parameters.

Second, to actually pass values to the stored procedure parameters, you call the Command object's Execute method, and pass your data in the method. The Execute method accepts stored procedure values in its second parameter, which must be an array. (In VBScript, you can create the array using the ARRAY() function.) The elements in the array correspond by position with parameters in the stored procedure, but are offset by one—the first input parameter of the stored procedure is actually the second array element, the second input parameter is the third array element, and so on. The first array element is reserved for the stored procedure's return value.

The description of how to do all this is actually longer than the script to accomplish it. Here's a sample in which a variable containing the name "Smith" is passed to a stored procedure called GetName.

Note Most values used with ADO methods and properties are numeric. For best readability and fewer errors, it is strongly recommended that you use constants in place of these numeric values. A full set of constants is defined in the files Adovbs.inc (for VBScript) and Adojavas.inc (for Microsoft JScript™), which you can add to your project and then include in your ASP files using the ASP #include directive.

<!-- #include adovbs.inc -->
<%
vLastName = "Smith"
' Create connection object, then open it
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DRIVER={SQL Server};SERVER=MyServer; _
   UID=sa;PWD=;DATABASE=Employees"
' Create command object, assign connection to it
Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "dbo.GetName"   'Name of SP
oCmd.CommandType = adCmdStoredProc   'ADO constant for 4
oCmd.Parameters.Refresh
' Create record set object by executing command object.
' Parameters for the stored procedure are passed in the 
' Command object's Execute method. The first element of 
' the array passed in the Execute method is reserved
' for the return value, so it is not used here.
Set oRs = oCmd.Execute(, Array(,vLastName))
%>

Passing input parameters using the Execute method is easy, but relies on implicit interactions between ADO and the database. You can control parameters more explicitly and efficiently using the Command object's Parameter collection.

Passing Input Parameters Using the Parameters Collection

The most efficient way to manage parameters for a stored procedure is to use the ADO Command object's Parameters collection. If you use a DTC to create a call to a stored procedure, you'll notice that the DTC-generated script uses the Parameters collection to pass parameters to the procedure.

Note Be careful if you use a DTC to generate script and then want to modify the script. If you use the visual designer to edit the DTC after you have changed the generated script manually, you will lose your changes. Either avoid making changes to the generated script, or if you do make changes, remove the HTML <METADATA> definition tag for the DTC so that you do not inadvertently use the visual designer.

The Parameters collection is like an array of parameters. Each parameter in the collection is a separate object that has its own properties, such as a name, data type, length, and value.

Figure 4. The Parameters collection

When you use the Parameters collection, you must already know the details about the parameters you want to pass, such as their data types. That's why the using Parameters collection is more efficient than calling the Refresh and Execute methods: ADO does not have to query the database to get this information.

To use the Parameters collection, you start by creating the usual ADO Connection and Command objects. You specify the stored procedure name as the Command object's CommandText property, and the value 4 (adCmdStoredProc) as its CommandType, as shown in the following example:

<!-- #include adovbs.inc -->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DRIVER={SQL Server};SERVER=MyServer; _
   UID=sa;PWD=;DATABASE=Employees"
Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "dbo.NewEmpl"   'Name of SP
oCmd.CommandType = adCmdStoredProc   'ADO constant for 4
%>

You then create a new Parameter object for each parameter that the stored procedure expects and add it to the Command collection. To create the Parameter object, call the Command object's CreateParameter method. You can pass the following information directly in the method, or if you prefer, set the corresponding object properties afterward:

Name. A name by which you will be able to refer to the Parameter object elsewhere in your script. This is optional.

Type. An ADO value indicating the data type of the parameter. Table 2 is a list of SQL Server data types and the corresponding ADO data type.

Table 2. SQL Server Data Types and Corresponding ADO Data Types

SQL Server Data Type ADO Data Type

binary adBinary (128)

varbinary adVarBinary (204)

char adChar (129)

varchar adVarChar (200)

datetime adDBTimeStamp (135)

smalldatetime adDBTimeStamp (135)

decimal adNumeric (131) (See note below)

numeric adNumeric (131)

float adDouble (5)

real adSingle (4)

int adInteger (3)

smallint adSmallInt (2)

tinyint adUnsignedTinyInt (17)

money adNumeric (131)

smallmoney adNumeric (131)

bit adBoolean (11)

timestamp adBinary (128)

text adLongVarChar (201)

image adLongVarBinary (205)

Note If you use a DTC, the DTC will map adDecimal (14) as the ADO data type of an SQL Server decimal. However, in some versions of ADO, you might experience difficulties with this mapping. If so, you need to substitute a different data type. For details, see "Fixing Decimal Data Types," later in this paper.

Direction. A property indicating whether you are passing a value to the stored procedure or getting one back. Table 3 describes direction values.

Table 3. Direction Values

Direction value Description

adParamInput (1) (Default) Passing data to stored procedure (input parameter)

adParamOutput (2) Passing data from stored procedure (output parameter)

adParamInputOutput (3) Bidirectional parameter

adParamReturnValue (4) Return value

Size. The maximum length of the data you intend to pass. For character data, this must be equal to or less than the maximum length that the corresponding stored procedure parameter will accept. This value is not required when you are defining numeric parameters.
Value. The actual value to pass to the stored procedure. This can be a literal value, the name of a variable, or an expression.

After creating a Parameter object, you add it to the Parameters collection by calling the collection's Append method. Here's a simple example:

<!-- #include adovbs.inc -->
Set oTmp = oCmd.CreateParameter("pEmpID", adInteger, _
   adParamInput,, 100)
oCmd.Parameters.Append oTmp

You could compress both steps (creating the parameter and appending it to the collection) into a single line using a command such as the following:

oCmd.Parameters.Append oCmd.CreateParameter("pEmpID",_
   adInteger, 1,, 100)

You must create and append a Parameter object for each parameter that the stored procedure expects. When you do, be sure that you create and append the parameters in the order that the stored procedure declares them, because when the stored procedure is called, the Parameter objects are passed by position.

Here's an example that creates three parameters. The first is an integer, the second a character string, and the third a date. For the sake of the example, the first parameter is passed as a literal value, the second as a variable, and the third as an expression. As each parameter is created, its object reference is stored in a temporary variable, which is then passed to the Append method to be added to the Parameters collection.

<!-- #include adovbs.inc -->
<%
Set oTmp = oCmd.CreateParameter("pEmpID", adInteger,_
   adParamInput,, 100)
oCmd.Parameters.Append oTmp

Set oTmp = oCmd.CreateParameter("pEmpLName", adChar,_
   adParamInput, 25, vLastName)
oCmd.Parameters.Append oTmp

Set oTmp = oCmd.CreateParameter("pEmpHireDate",_
   adDBTimeStamp, adParamInput,,_
   Request.Form("HireDate"))
oCmd.Parameters.Append oTmp
%>

After the parameters are created and appended to the Command object's Parameters collection, you can create and open a result set using that command. Opening the result set passes the parameters to the stored procedure, and then executes the procedure. For example, you could add the following line immediately after the preceding example:

<%
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
%>

After creating parameters and appending them to the Parameters collection, you can change their values at any time, which is useful if you want to call the same stored procedure multiple times in the same page. To change a parameter, set its Value property to a new value, as in this example:

oCmd.Parameters("pEmpID").Value = 100

Alternatively, you can reference the parameter by its position within the collection:

oCmd.Parameters(1).Value = 100

An even more abbreviated version simply specifies the positional value without even referencing the Parameters collection at all:

oCmd(1) = 100

The last method is the most compact way to reference parameters, but is less readable and can therefore result in errors.

As an example of how to reference and change parameters, the following script creates a small report of insurance policies using two result sets. The first result set shows the policies with claims in 1995 and the second with claims in 1996. After the first record set is displayed, it is closed. The parameter values are changed, and then the record set it opened (and thus executed) again:

<%
' [Script to create ADO objects not included here]
vStartDate = "Jan 1, 1995"
vEndDate = "Dec 31, 1995"
Set tmpParam = oCmd.CreateParameter("pStartDate", 135,_
   1,,vStartDate)
oCmd.parameters.append tmpParam
Set tmpParam = oCmd.CreateParameter("pEndDate", 135,_
   1,,vEndDate)
oCmd.parameters.append tmpParam
oRs.Open oCmd
%>
<h2>Claims in 1995</h2>
<%Do until oRs.EOF%>
   <P>PolicyID = <% =oRs.Fields("PolicyID")%><br>
   Date = <% =oRs.Fields("ClaimDate")%></P>
   <%oRs.MoveNext%>
<%Loop%>
<%oRs.Close%>

<!-- The parameter values are now changed and the 
     record set opened again. -->
<%
oCmd.Parameters("pStartDate").Value = "Jan 1, 1996"
oCmd(1) = "Dec 31, 1996"      ' Alternate syntax 
oRs.Open oCmd
%>
<h2>Claims in 1996</h2>
<%Do until oRs.EOF%>
   <P>PolicyID = <% =oRs.Fields("PolicyID")%><br>
   Date = <% =oRs.Fields("ClaimDate")%></P>
   <%oRs.MoveNext%>
<%Loop%>
<%oRs.Close%>

Fixing Decimal Data Types

If the stored procedure you are calling includes a parameter with the SQL Server data type of decimal, money, or smallmoney, you might experience difficulty if you use the ADO data type adDecimal (14) when creating a Parameter object. DTCs assign the ADO data type 14 if they detect a decimal data type in the stored procedure, so you're particularly likely to see the problem if you've used a DTC.

You'll know you have the problem if the stored procedure has one of the decimal data types and you see this error when requesting the Web page that calls the stored procedure:

At least one parameter contained a type that was not supported.

To make the fix, you need to find the line in which the decimal parameter is created. In the script generated by the DTC, look for calls to the CreateParameter method. For example, you might find a line like this:

Set tmpParam = cmdTemp.CreateParameter("@vSalary",
   14, 1, 4, [vSalary])

In the CreateParameter method, the second parameter is the data type of the data you are passing. You will find the value 14 there for decimal and money data types. Substitute the ADO data type adNumeric (131), and the stored procedure will work fine.

Getting Output Parameter Values from a Stored Procedure

If the stored procedure you are calling returns a value—either the return value or an output parameter declared explicitly in the stored procedure—you can get the value to use in your Web application. Getting the value of an output parameter is similar to passing an input parameter value to the procedure, but requires an extra step or two.

The first thing to do is to determine whether your stored procedure generates a result set. Some don't; they only calculate a value and return that. Either way, you have to know whether there will be a result set, because that will determine how you can get the value of the output parameters.

As with passing parameters, there are different ways to get output parameters:

Using a DTC to set up a call to the stored procedure. However, even if you use the DTC, you might still need to modify some of the generated script.
Manually setting up the parameters using the Command object's Parameters collection.

Whichever way you choose, you must then add some script to extract the value of the parameters after the stored procedure has run.

Using a DTC to Fetch Output Parameter Values

To use a DTC to call a stored procedure that returns output parameter values, you use the same basic technique that you use when the stored procedure requires input parameters. (For details, see "Specifying Input Parameter Values in a DTC" earlier in this paper.)

One difference is that when you click on the Command tab of the Properties window, you see the output parameters marked with and the return value marked with . The Command tab allows you to specify an expression (most logically, a variable) for the output parameter. However, because you won't use any variables that you specify here, there's no real need to enter anything for the output parameters.

When you save the DTC, it generates script to call the stored procedure and manage the parameters. However, it does not complete the job for you. You must still add script to actually extract the value of the individual output parameters (and if you like, the stored procedure's return value).

In addition, if the stored procedure produces a result set, you have to add some script to handle SQL Server output parameter values, because SQL Server does not return output parameter values until after any result sets produced by the stored procedure have been processed.

At this point, therefore, you are at the same point as if you had scripted the stored procedure call yourself and set up the Command object's Parameter collection, as described in the next section. For details about how to process the result set and extract the parameter values, see "Extracting the Values of Output Parameters," later in this paper.

Using the Parameters Collection with Output Parameters

You can use the Command object's Parameters collection to set up output parameters in almost the same way that you set up input parameters. (For the basics about using the Parameter collection, see "Passing Input Parameters Using the Parameters Collection" earlier in this paper.)

When you use the CreateParameter method to create a Parameter object for an output parameter, the differences from creating an input parameter are these:

The Name property is handier, because you can use it later when extracting the value of the parameter. If you use a DTC, a name is assigned for you based on the name of the parameter in the stored procedure. However, there is no particular relationship between parameter names in the ADO parameter and the stored procedure, and the names don't have to match. In fact, names are optional.
The Direction property is set to 2, indicating an output parameter. You might also set it to 3 (bidirectional) if you are using the same parameter both to pass and receive data. If you are creating a Parameter object to get the stored procedure's return value, set the direction to 4.
The Value property isn't passed in the CreateParameter method, since the stored procedure will set it.

The order in which you create the ADO parameters is important, as it is when setting up to pass input parameters—they are matched up between the Parameters collection and the stored procedure in the order they are declared in the stored procedure. If you create a parameter for the stored procedure's return value, it must always be the first element (element zero) in the collection.

The following example shows how you might set up an ADO parameter to get the value of a stored procedure's return value and one output parameter:

<!-- #include adovbs.inc -->
<%
Set oTmp = oCmd.CreateParameter("Return Value", _
   adInteger, adParamReturnValue)
oCmd.Parameters.Append oTmp
Set oTmp = oCmd.CreateParameter("vAverageSales",_
   adInteger, adParamOutput)
oCmd.Parameters.Append oTmp
%>

After setting up the Parameters collection, you can execute the stored procedure. As with input parameters, you have two choices:

Call the Command object's Execute method. This is a good choice if the stored procedure does not return a result set. For example:

<!-- #include adovbs.inc -->
<%
Set oTmp = oCmd.CreateParameter("vAverageSales",_
   adInteger, adParamOutput)
oCmd.Parameters.Append oTmp
oCmd.Execute
%>

Create a Recordset object, and call its Open method or the Command object's Execute method. The Open method allows you more flexibility, such as maintaining a bidirectional cursor in the result set. You must use one of these two methods if the stored procedure returns a result set. If you use a DTC, you'll see that the DTC creates a Recordset object and calls its Open method, because the DTC does not check that a stored procedure creates a result set. For example:
```

<%
Set oTmp = oCmd.CreateParameter("vAverageSales",_
   adInteger, adParamOutput)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
%>
```

Extracting the Values of Output Parameters

After the stored procedure has run, you can extract the return value and the value of the output parameters. If the stored procedure does not return a result set, and if you scripted a call to it using the Command object's Execute method, you're all set. However, you have to manage the result set if either of the following applies:

You scripted the call yourself and used the Recordset object's Open method to create the result set.
You used a DTC to set up a call to the stored procedure, whether or not the stored procedure returned a result set.

If the stored procedure returns a result set, you probably want to use that result set in your application. Therefore, you should add the necessary script, or use a DTC to manage the result set for you. Either way, following the script that uses the result set, you must close the result set, using script such as this:

<%oRs.Close%>

If you have used a DTC, your line of script will probably look more like this:

<%Datacommand1.Close%>

Be sure that you put this script after the last possible reference to the result set; otherwise, ADO will display an error when you next attempt to access the result set. Also be sure to put this line outside of the metadata definitions for any DTCs you are using, or the line will be overwritten the next time you use the visual designer to edit the DTC.

The other possible situation is that the stored procedure does not return a result set, but your script has run the stored procedure by calling a Recordset object's Open method. If you used a DTC, this is your situation, because the DTC had no way of knowing that the stored procedure wouldn't produce a result set.

You have a couple of choices. One is to root out the call to the Open method and substitute a call to the Command object's Execute method. The disadvantage is that you are then changing script generated by the DTC, with the danger of losing your changes if you ever again use the visual designer to edit the DTC.

A safer choice is to add some script that removes the (nonexistent) result set, and then throws away the Recordset object. Add some lines like the following:

<%Datacommand1.Open cmdTemp%>
[...]
' The following script should appear outside the DTC
' metadata definition
<%While Not Datacommand1 Is Nothing
   Set Datacommand1 = Datacommand1.NextRecordset()
Wend
%>

Now you're ready to get the output parameters. To do so, get the value of the parameter's Value property, something like this:

Last name = <%=oCmd.Parameters("vAverageSales").Value%>

Here's an example for extracting a parameter if you're using a DTC:

Last name=<%=Datacommand1.Parameters_
   ("@vAverageSales").Value%>

You can refer to the parameter you want by the name you assigned it when you created it, or by its position in the Parameters collection, as in the following example:

Last Name = <%=oCmd.Parameters(1).Value%>

If you use a positional reference, remember that the collection begins with element zero. You'll also have to remember where each parameter is in the collection—and don't forget that the stored procedure's return value is often in element zero.

Summing Up

Calling stored procedures is an efficient way to manage data in your Web applications, made all the more powerful by the ability to pass parameters to and from the procedure. In Visual InterDev, you can use DTCs to help you call stored procedures, or you can script the call yourself using ADO objects. Passing parameters does not add much complexity to the ordinary methods of accessing a database, but does require that you understand how to set up a DTC or your own script both to pass and receive parameters. By mastering the few details required to successfully pass parameters, you can manage stored procedures with ease.

SQL Server Data Type	ADO Data Type
binary	adBinary (128)
varbinary	adVarBinary (204)
char	adChar (129)
varchar	adVarChar (200)
datetime	adDBTimeStamp (135)
smalldatetime	adDBTimeStamp (135)
decimal	adNumeric (131) (See note below)
numeric	adNumeric (131)
float	adDouble (5)
real	adSingle (4)
int	adInteger (3)
smallint	adSmallInt (2)
tinyint	adUnsignedTinyInt (17)
money	adNumeric (131)
smallmoney	adNumeric (131)
bit	adBoolean (11)
timestamp	adBinary (128)
text	adLongVarChar (201)
image	adLongVarBinary (205)

Direction value	Description
adParamInput (1) (Default)	Passing data to stored procedure (input parameter)
adParamOutput (2)	Passing data from stored procedure (output parameter)
adParamInputOutput (3)	Bidirectional parameter
adParamReturnValue (4)	Return value