Page 515 - Microsoft Office Excel 2003 Programming Inside Out

Introducing ADO

Inside Out

Stored Procedures
Stored procedures are merely precompiled routines available on the database server that
someone can execute to perform a database task. Although Access doesn’t support stored
procedures, many other database systems, such as SQL Server, Oracle, and DB2, all sup-
port stored procedures.

Typically, stored procedures are written using SQL statements connected together with
other statements, such as If statements, looping statements, Print statements, and so on.
Each vendor has its own syntax for these statements, so stored procedures aren’t portable
from one database system to another. However, stored procedures are much faster than
simply executing an SQL statement. When stored procedures are created on the database
server, they are stored in a precompiled form, which saves a lot of resources because the
SQL statements need not be compiled each time you execute a database command.
Stored procedures are in many ways like subroutines. You can create stored procedures
with a list of parameters and pass values for each parameter when you run the command.
Again, this has a big impact on speed and throughput, both of which are a big concern to
most database administrators. Finally, stored procedures offer another way to secure
access to the database. In these days when computer hackers commonly attack all kinds
of computers, many people consider security even more important than performance.

Using the Parameters Collection
The Parameters collection contains information about the parameters associated with a
Command object. (See Table 23-6.)
Table 23-6. Properties and Methods of the Parameters Collection
Property/Method Description

Append(Parameter) Method: appends the specified Parameter object to the collection.
Count Property: returns the number of items in the collection.
Delete(index) Method: removes the Parameter object with the specified index. Chapter 23
Item(index) Property: returns the Parameter object at the location specified by
index.
Refresh Method: connects to the database and retrieves a copy of the
parameter information for the stored procedure specified in
CommandText.

The Parameters collection is essentially a normal collection object with two differences. The
Append method is used to add a new Parameter object to the end of the collection. Depending
on how the parameters are defined in the command, the order can be very important.

489
Part 6: Excel and the Outside World: Collaborating Made Easy