Using Stored Procedures

If you are using an existing client/server database, you’ll likely find that the administrator has created many stored procedures that you can use. Stored procedures are compiled code written in the server’s native programming language (generally the server-specific dialect of SQL) stored on the server that are used for a wide variety of operations. Their main purpose is to build logic into the processing of SQL statements. For instance, if you want to select data from one of two tables on the server, depending upon a value from a third table, a stored procedure would be an ideal candidate. Pass-through queries allow you to use stored procedures.

Stored procedures on the server are generally faster than dynamic SQL statements, just as stored queries in Microsoft Jet are faster than dynamic SQL. That is, a stored procedure is usually a precompiled execution plan. No parsing needs to occur, only execution.

When your application is waiting for user input, it isn’t worth the added complication of stored procedures to increase the speed, because neither the user nor the server benefits from the increased speed. Stored procedures really become useful when more complex logic is required — for example, when satisfying a business rule that a particular book has to be in stock and then checking to see if more books should be ordered on each purchase.

You can add records by using DAO code that uses the Edit or AddNew method along with the Update method on a dynaset-type Recordset object. When combined with the dbAppendOnly constant in the options argument of the OpenRecordset method, this is an efficient and simple technique. For specialized scenarios, however, you may find that it’s worth creating your own stored procedures.

The BookSalesOptimized sample application creates the following stored procedure on the SQL Server when you run it:

CREATE PROCEDURE InsertSale(@stor_id char(4), 
@ord_num varchar(20), 
@ord_date datetime, 
@qty smallint, 
@payterms varchar(12), 
@title_id varchar(6))
AS
BEGIN
INSERT INTO sales VALUES (@stor_id, @ord_num, @ord_date, @qty, @payterms, @title_id)
RETURN
END

This stored procedure adds a new record to the Sales table. You can call this procedure by using the Execute method of a Database object with the dbSQLPassThrough constant specified in the options argument, and supplying a value for each of the six parameters. The values you supply for parameters are inserted into fields in the table.

For example, you could call the InsertSales stored procedure as follows, where dbs is a Database object representing the remote database, and strStoredProcCall is a string variable:

strStoredProcCall = "InsertSale '6380', 'SALES2', '5/6/97 7:16:12 PM'", _
	& "1, 'Net 30', 'PS2091'"
dbs.Execute strStoredProcCall, dbSQLPassThrough

See Also For more information about queries, see “Using SQL Pass-Through Queries” earlier in this chapter. You can use input parameters and return values with stored procedures when using ODBCDirect QueryDef objects. For more information, see “The Parameter Object and the Parameters Collection” and “Working with Stored Procedures” later in this chapter.