So why should we create and use a stored procedure instead of just creating a SQL string on the fly, as in the example shown above? Well, there are several reasons:

• A stored procedure is compiled by the database. This produces an execution plan, so the database knows exactly what it's going to do. This makes the execution of the procedure faster.
• Stored procedures are often cached by the database, thus making them faster to run, as they don't have to be read from disk. Not all databases support this caching mechanism - Microsoft Access doesn't, but SQL Server does.
• You can make your data a little bit more secure by specifying that your database tables can be modified only by stored procedures. This means that potentially dangerous SQL operations generated on the fly may not be performed.
• You avoid cluttering your ASP code with lengthy SQL statements. This makes the ASP code easier to maintain.
• You can keep all of the SQL code together, on the server.
• You can use output parameters in a stored procedure, which allows you to return both a recordset and other values.

As a general rule, stored procedures will nearly always be quicker than their equivalent SQL statements.

To use a stored procedure you just put the name of the stored procedure as the command text, and set the type accordingly. For example, consider the previous example of updating book prices. If we created a stored procedure on SQL Server, it might look like this:

CREATE PROCEDURE usp_UpdatePrices
AS
  UPDATE Titles
  SET  Price = Price * 1.10
  WHERE Type='Business'

For a Microsoft Access database you can create a simple update query to do the same task:

To run this stored procedure from an ASP page, you'd simply use the following code:

Set cmdUpdate = Server.CreateObject("ADODB.Command")

cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "usp_UpdatePrices"
cmdUpdate.CommandType = adCmdStoredProc

cmdUpdate.Execute , , adExecuteNoRecords

This simply runs the stored procedure. No recordset is returned, because we are only updating data - remember, there's no point creating a recordset unless one is needed.

As it stands though, this procedure isn't very flexible since it only deals with one book type. What would be better would be a procedure that allows us to select the book type so we don't have to create a procedure for each book type. And while we're at it, we might as well remove the fixed 10% update, and allow this to be flexible too. So, how do we achieve this - simple, with parameters.

Parameters

Parameters to stored procedures are just like parameters (or arguments, depending on your preferred term) to procedures and functions. These allow values to be passed into a function, and then the function can use the value. Stored procedures in SQL Server (and other databases, including Access) both have this facility.

To allow the procedure to cope with multiple book types, and even allow the user to specify the price increase (or decrease), we need to add some parameters:

CREATE PROCEDURE usp_UpdatePrices
 @Type   Char(12),
 @Percent  Money

AS
  UPDATE Titles
  SET  Price = Price * (1 + @Percent / 100)
  WHERE Type = @Type

The stored procedure usp_UpdatePrices now takes two parameters:

• One for the book type (@Type)
• One for the percentage change in price (@Percent)

Just like a VBScript function, these parameters are variables. However, unlike VBScript and other scripting languages where all the variables are variants, SQL variables all have specific types (Char, Money, etc). They must also follow the naming convention for SQL variables, which means they must start with an @ symbol.

Notice that we've allowed the percentage to be supplied as a whole number (for example 10 for 10%), instead of it's fractional value. This just makes the procedure more intuitive to use.

The Parameters Collection

So now we have a stored procedure with parameters, how do we actually call this from ADO? You've already seen how to call stored procedures without parameters using the Command object, and that doesn't change. What changes is the use of the Parameters collection.

The Parameters collection contains a Parameter object for each parameter in the stored procedure. However, ADO doesn't automatically know what these parameters are, so you have to create them, using the CreateParameter method, which takes the following form:

Set Parameter = Command.CreateParameter (Name, [Type], [Direction], 
                       [Size], [Value])

The arguments are:

Once the parameter is created it can be appended to the Parameters collection. For example:

Set parValue = cmdUpdate.CreateParameter("@Type", adVarWChar, _
                     adParamInput, 12, "Business")
cmdUpdate.Parameters.Append parValue

Set parValue = cmdUpdate.CreateParameter("@Percent", adCurrency, _
                     adParamInput, , 10)
cmdUpdate.Parameters.Append parValue

There's no need to explicitly create an object to hold the parameter - the default type of Variant works well enough here. If you don't want to create a variable, you can also take a shortcut:

cmdUpdate.Parameters.Append = _
  cmdUpdate.CreateParameter("@Percent", adCurrency, _
                adParamInput, , 10)

This uses the fact that the CreateParameter method returns a Parameter object, and the Append method accepts a Parameter object. This method is marginally faster than using a variable, but does make your lines of code longer and therefore harder to read. You can use whichever method you prefer.

You don't have to set the value of the parameter at the time you create the parameter, since once the parameter is appended to the Parameters collection it remains in the collection. You can therefore set the value any time before the command is run. For example:

cmdUpdate.Parameters.Append = _
   cmdUpdate.CreateParameter ("@Percent", adCurrency, adParamInput)

cmdUpdate.Parameters("@Percent") = 10

In the previous chapter we mentioned that there are several ways of accessing values in collections, and the Parameters collection is no different. The above example uses the name of the parameter to index into the collection, but you could equally use the index number:

cmdUpdate.Parameters(0) = 10

This sets the value of the first (collections are zero-based) parameter in the collection. The index number method is marginally faster that the name method, but obviously the name method makes your code much clearer to read.

One point that is important to note is that the parameters in the Parameters collection must match the order of the parameters in the stored procedure.

Continued...