Working with Stored Procedures

You can use ODBCDirect QueryDef objects to run stored procedures. ODBCDirect QueryDef objects support stored procedures that have both input parameters and return values. Input parameters are the parameter values supplied to the procedure at run time. The procedure’s return value is the value that it returns when it has finished running. For example, a stored procedure may return the number of records that have been affected.

The following example creates a stored procedure named GetEmps on the server, where strSQL is a string variable and cnn is a Connection object:

strSQL = "CREATE PROCEDURE GetEmps AS " _
	& "SELECT * FROM EMPLOYEE;"
cnn.Execute strSQL

If there is already a stored procedure named GetEmps on the server, you can use the DROP statement to delete it before creating a new one, as shown in the following example.

strSQL = "DROP PROCEDURE GetEmps;"
cnn.Execute strSQL

You can run the stored procedure by using the Execute method of a Connection object. To retrieve the return value, create a QueryDef object and open a recordset on it. In this example, qdf is a QueryDef object and rst is a Recordset object:

Set qdf = cnn.CreateQueryDef("qry", "{ call GetEmps() }")
Set rst = qdf.OpenRecordset

Use the Parameter object to work with parameters. The Direction property of a Parameter object tells DAO how the parameter will function. The ODBC driver tries to determine the parameter direction, but the Direction property is read/write, so you can set it if you need to. The following example creates a simple stored procedure with an input parameter and a return value. It then runs the procedure and retrieves the return value into a variable of type Variant, varX.

' Create stored procedure on the server.
strSQL = "CREATE PROCEDURE UpdateEmps (@invar int) AS RETURN @invar;"
cnn.Execute strSQL

' Create QueryDef object to run stored procedure.
Set qdf = cnn.CreateQueryDef("qry", "{ ? = call UpdateEmps(?) }")

' Handle parameters.
qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1) = 10
qdf.Execute

' Get return value.
varX = qdf.Parameters(0).Value