ASP 3.0 advanced programming)

9.2.3 stored procedure
The use of stored procedures is a field in which Command objects are applied. Stored Procedures (sometimes referred to as stored queries) are pre-defined SQL query statements stored in the database.
Why should we create and use stored procedures instead of using SQL strings directly in code? There are mainly the following reasons:
· The stored procedure has been compiled by the database. In this way, an execution plan can be generated, so the database knows exactly what it will do, which speeds up the execution of the process.
· Stored procedures are usually cached by the database at high speed to make them run faster because they do not need to be read from the disk at this time. Not all databases support this cache mechanism. For example, Microsoft Access does not support this mechanism, but SQL Server does.
· By specifying that tables in the database can only be modified by stored procedures, data security can be ensured. This means that SQL operations that are potentially dangerous will not be executed.
· You can avoid mixing ASP code with lengthy SQL statements, making ASP code easier to maintain.
· All SQL code can be stored on the server.
· You can use output parameters in stored procedures to return record sets or other values.
Generally, stored procedures are almost always faster than SQL statements.
To use a stored procedure, you only need to use the name of the stored procedure as the command text and set the corresponding type. For example, consider the previous example of updating the book price. If you create a stored procedure on SQL Server, you can write the following code:
Create procedure usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
Where type = 'business'
For Microsoft's Access database, you can use a simple update query statement to complete the same task, as shown in Figure 9-1:
Figure 9-1 update query using Microsoft's Access Database
To run the stored procedure on an ASP webpage, you only need to use the following code:
Set cmdUpdate = Server. CreateObject ("ADODB. Command ")
CmdUpdate. ActiveConnection = strConn
CmdUpdate. CommandText = "usp_UpdatePrices"
CmdUpdate. CommandType = ad1_storedproc
CmdUpdate. Execute, adExecuteNoRecords
This is just the process of running the stored procedure. No record set is returned, because data is only being updated. Remember not to create a record set unless you do.
Although this can also be done, it is not very flexible, because it only handles one type of books. A better way is to create a process that allows us to select the book type, so that we do not have to create a process for each category of books. You can also remove a fixed 10% update, which improves flexibility. So, how can we achieve this? It's very easy to use parameters.
1. Parameters
The stored procedure parameters (or variables) are the same as the general procedure and function parameters. They can be passed to the function, and then the function can use their values. The stored procedures in SQL Server (also in other databases, including Access) have such functions.