Accessing Data With the Stored Procedures Model

The Stored Procedures model assumes the following steps:

1. You request the provider to run an already compiled stored procedure.

2. The compiler runs the stored procedure.

3. The compiler returns the results of the stored procedure to you.

You can implement a Stored Procedures data-access model in one of several ways:

• Call a Connection object's Execute method with a single argument that is the name of the stored procedure that you want the provider to execute. The Connection object's Options argument must be set to adCmdStoredProc.

• Call a Recordset object's Open method with the name of the stored procedure. The Recordset's Options argument must be set to adCmdStoredProc.

• Call a Command object's Execute method after setting the Command object's CommandText property to the name of the stored procedure. Either the Execute method's Options argument (third argument) must be adCmdStoredProc, or the Command object's CommandType property must be adCmdStoredProc. You may also have to set the Command object's Parameters collection if the stored procedure requires or returns parameters.

Listing 9.2 gives an example of a stored procedure in a SQL Server database, and Listing 9.3 gives examples of the Stored Procedures data-access model in VB.

LISTING 9.2
A STORED PROCEDURE IN SQL SERVER

create procedure Titles_All
AS
Select * from Titles Order By title
GO

LISTING 9.3
EXAMPLES OF THE STORED PROCEDURES MODEL IN VB

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