Using Stored Procedures to Return Records to an Application

Whenever your application requires one or more records from the data, you will definitely use a cursor, whether you choose a stored procedure, or whether you choose an inline SQL statement.

The text of a stored procedure that returns records can look just like the text of an inline SQL statement (although it may also use more complicated logic than a simple SQL statement). Listing 9.15 gives an example of a simple stored procedure that returns records.

LISTING 9.15
A SQL STATEMENT THAT WILL CREATE A STORED PROCEDURE TO RETURN ROWS IN SQL SERVER

create procedure Publishers_All
AS
Select * from publishers Order By pub_name
GO

There are several methods for getting records back from a stored procedure. These methods are discussed here at greater length.

Before comparing the three following methods, bear in mind that all ADO techniques for handling rows of data end up with a Recordset object. Therefore, the three following methods are really just three different ways of populating a Recordset object's rows.

To get records back from a Connection object (see Listing 9.16), execute the following steps:

STEP BY STEP
9.2 Getting Records Back From a Connection Object

1. Make sure that the Connection object is either open or has a valid ConnectionString property.

2. Set a Recordset object variable to the results of the Connection object's Execute method.

3. When calling the Execute method, pass the following arguments to the method (or set the corresponding properties of the Connection object):
   
   • CommandText. The name of the stored procedure as a text string.
   
   • RecordsAffected (optional). A long variable that the provider will fill with the number of records affected by this query.
   
   • Options. Always set options to adCmdStoredProc when the CommandText argument represents a stored procedure name.

After you have executed the preceding steps, the Recordset object should be populated with the records returned by the stored procedure.

LISTING 9.16
USING A CONNECTION OBJECT TO RETURN RECORDS FROM A STORED PROCEDURE

Set rsPubs = connPubs.Execute "Titles_All", , adCmdStoredProc

To get records back from a stored procedure using a Command object (see Listing 9.17), execute the following steps:

STEP BY STEP
9.3 Getting Records Back From a Stored Procedure Using a Command Object

1. Set the Command object's CommandType property to adCmdStoredProcedure.

2. Set the CommandText property to a string representing the stored procedure's name.

3. Prepare the stored procedure's parameters by using the Command object's Parameters collection.

4. a. Set a Recordset object variable to the results of the Command object's Execute method,
   
   or
   
   b. Run the Execute method without setting the result to point to a Recordset. Instead, make the Recordset's ActiveCommand property point to the Command object, and then call the Recordset's Open method.

5. In the call to the Command object's Execute method, you can pass the following arguments (all optional) to the method:
   
   • RecordsAffected. A long variable that the provider will fill with the number of records affected by this query.
   
   • Parameters. A variant array of values to pass as parameters to the stored procedure. Use this argument as an alternative to setting up the Command object's Parameters collection. Note that output parameters will not return the correct values when you use this Parameters argument.
   
   • Options. Use this argument as an alternative to set the CommandType property. Always set to adCmdStoredProc when the CommandText argument represents a stored procedure name.

6. After you have executed the preceding steps, the Recordset object should be populated with the records returned by the stored procedure.

LISTING 9.17
USING A COMMAND OBJECT TO RETURN RECORDS FROM A STORED PROCEDURE

cmdPubs.CommandText = "Titles_All"
cmdPubs.CommandType = adCmdStoredProc
Set rsPubs = cmdPubs.Execute

To get records back from a stored procedure into a Recordset directly without using the Execute methods of Connection or Command objects, call the Recordset's Open method. Make sure that first you set the appropriate properties or pass it the name of the stored procedure as its Source argument (first argument) and adCmdStoredProc as its Options argument (fifth argument) (see Listing 9.18).

LISTING 9.18
RETURNING RECORDS DIRECTLY INTO A RECORDSET FROM A STORED PROCEDURE

rsEmployees.CursorType = giCursorType
rsEmployees.CursorLocation = giCursorLocation
rsEmployees.LockType = giLocking
rsEmployees.Source = _
"Select * From Employees Order By LastName,FirstName"
Set rsEmployees.ActiveConnection = cnNWind
rsEmployees.Open