This example walks you through the steps needed to execute a stored procedure that takes two parameters. Stored procedure parameters can be input, output, or both input and output. In most cases, the ODBC driver can automatically determine the type of parameter and correctly assign it to the Direction property. Our example executes the sp_password procedure, which can be used in SQL Server systems to change a user’s password. This procedure assumes the password to be changed belongs to the current user because only the system administrator can change other users' passwords.
This procedure query accepts two input parameters and passes back a return value. You could use the Execute method to run this query, but the return value would be lost: You would have no easy way of telling if the password change was successful. To capture the return value and create an rdoQuery that can be used repeatedly to change the user password, write code as shown below:
Dim CPw As rdoQuery, QSQL As String
QSQL$ = "{ ? = call sp_password (?, ?) }"
The next line of code creates the rdoQuery and names it SetPassword. The SQL property is set with the QSQL query defined above. This line only needs to be executed once. The new rdoQuery object is automatically appended to the rdoQueries collection, where it can be recalled later.
Set CPw = cn.CreateQuery("SetPassword",QSQL$)
The next step sets the Direction property to indicate that the parameter is used for input, output, or both. The default Direction is rdParamInput, but in most cases it is unnecessary to set the Direction property at all, because the ODBC driver can determine this value from the stored procedure's definition.
The ordinal number of the parameters is based on the order in which they appear in the SQL statement. In this case, the “0th” parameter is the return value (? = ), the “1st” is the first input parameter, and the “2nd” is the second input parameter. As you can see, the rdoParameters collection is zero-based. The code shown below is actually referencing the rdoParameters collection, which is the default collection for the rdoQuery object.
Cpw.rdoParameters(0).Direction = rdParamReturnValue
This next step sets the two input parameters that RDO inserts into the query when it is executed. Note that rdoParameters is the default collection and is implied in the following code.
Cpw(1) = "clyde" ' Set the first input parameter.
Cpw(2) = "framis" ' Set the second input parameter.
Once the parameter direction and values are set, you can use the Execute method to run the query if it does not return rows, or the OpenResultset method if the procedure contains one or more SELECT statements:
Cpw.Execute()
Once the procedure is executed, you can examine the rdoParameters collection for the returned value:
If Cpw.rdoParameters(0) <> 0 Then
Msgbox "Could not change password"
End If