Recommended: ASP 3.0 Advanced Programming (Twelve) 4.2.3 Examples of server-side containing instructions This section provides some example pages that can be used to experiment with various server-side containing statements. Open the subdirectory of the sample web page Chapter 04, displaying SSI Directives and the ASP Server Object&rdqu
9.2.3 Stored proceduresThe use of stored procedures is an area where Command objects are applied. Stored procedures (sometimes called storage queries) are SQL query statements that are predefined in a database.
Why should you create and use stored procedures instead of using SQL strings directly in your code? There are several main reasons:
· Stored procedures have been compiled by the database. This results in an execution plan, so the database knows exactly what it will do, thus speeding up the execution of the process.
Stored procedures are usually cached by the database, which makes them run faster because they are not required to be read from disk at this time. Not all databases support this caching mechanism. For example, Microsoft's Access does not support it, but SQL Server supports it.
· By specifying that tables in the database can only be modified by stored procedures, the data can be ensured to be more secure. This means that potentially dangerous SQL operations will not be performed.
· ASP code can be avoided mixing together with lengthy SQL statements, making ASP code easier to maintain.
· All SQL code can be stored centrally on the server.
· The output parameters can be used in stored procedures to allow the return of the record set or other values.
Generally speaking, stored procedures are almost always faster than comparable SQL statements.
To use stored procedures, just use the stored procedure name as command text and set the corresponding type. For example, consider the previous example of an updated book price. If you create a stored procedure on SQL Server, you can write code:
CREATE PROCEDURE usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
WHERE TYPE='Business'
For Microsoft's Access database, the same task can be accomplished using a simple update query statement
To run this stored procedure in an ASP web page, just use the following code:
Set cmdUpdate = Server.CreateObject(ADODB.Command)
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = usp_UpdatePrices
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.Execute , , adExecuteNoRecords
This is just running a stored procedure. No record set returns because the data is just being updated. It is important to remember that you do not create recordsets unless you really need them.
While this is OK, it is not very flexible, as it is only dealing with one type of book. A better approach is to create a process that allows us to choose a book type so that we don’t have to create a process for each type of book. Also, the fixed 10% update can be removed, which makes for better flexibility. So, how can I do this? It's very simple, use parameters.
1. parameter
The parameters (or variables) of stored procedures are the same as those of general procedures and functions. They can be passed into the function, and the function can then use its value. Stored procedures in SQL Server (the same is true for other databases, including Access) have this function.
In order for stored procedures to handle multiple types of books, and even allow users to specify an increase (or decrease) of prices, some parameters need to be added:
CREATE PROCEDURE usp_UpdatePrices
@Type Char(12),
@Percent Money
AS
UPDATE Titles
SET Price = Price * (1 @Percent / 100)
WHERE Type = @Type
Now, the stored procedure usp_UpdatePrices takes two parameters:
· One is the type of book (@Type).
· One is the percentage of book price changes (@Percent).
Like VBScript's functions, these parameters are variables. However, unlike VBScript and other scripting languages, the variables in these scripting languages are all variable types, while SQL variables have definite types (char, Money, etc.). The naming specification of SQL variables must be followed, that is, the variable must start with the symbol @.
Note that we have percentages passed into this process as an integer (such as 10 means 10%) instead of as a fractional value. This just makes stored procedures a little more intuitive.
2. Parameters Collection
So, now there is a stored procedure with parameters, but how do you call it through ADO? We've seen how to call stored procedures without parameters with Command objects, and in fact, there's no difference between them. The difference is the use of Parameters collections.
The Parameters collection contains Parameter objects for each parameter in the stored procedure. However, ADO does not automatically know what these parameters are, so they must be created using the CreateParameter method, in the following form:
Set Parameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])
The parameters and descriptions are shown in Table 9-3:
Table 9-3 Parameters and descriptions of CreateParameter method
parameter
illustrate
Name
Parameter name. This is the parameter name in the Parameters collection, not the parameter name in the stored procedure. However, using the same name is a good practice
Type
The data type of the parameter. It can be an adDataType constant, see the appendix for details
Direction
The direction of the parameter indicates whether the parameter provides information to the stored procedure or the stored procedure returns information to the ADO. It can be one of the following values:
adParamInput, the parameter is the input parameter passed to the stored procedure
adParamOutput, the parameter is the output parameter retrieved from the stored procedure
adParamInputOutput, the parameters can be used as both input and output parameters
adParamReturnValue, this parameter contains the status returned by the stored procedure
Size
Parameter length. For fixed-length types, such as integers, this value can be ignored
Value
Value of parameter
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 is no need to explicitly create an object to save parameters, the default Variant type can already work quite well. If you don't want to create a variable, you can also take shortcuts, such as the following code:
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter(@Percent, adCurrency, adParamInput, , 10)
This uses the CreateParameter method to return a Parameter object and receives it with the Append method. This method runs faster than using variables, but lengthens the line of code, making it less readable. You can choose one of these methods according to your hobbies.
After the parameters are added to the Parameters collection, they are retained in it, so each parameter is not necessarily assigned a value when the parameter is created. The value of the parameter can be set any time before the command is run. For example:
cmdUpdate.Parameters.Append = _
cmdUpdate.CreateParameter(@Percent, adCurrency, adParamInput)
cmdUpdate.Parameters(@Percent) = 10
The previous chapter mentioned that there are several ways to access values in a collection, and the Parameters collection is no different. The above example uses the name of the parameter to retrieve parameters in the set, or can also be searched using index numbers:
cmdUpdate.Parameters(0) = 10
The above code assigns the first parameter in the parameter set (the Parameters set numbered from 0)
Share: Related knowledge about advanced ASP Error objects for ASP application In VBScript, there is an OnErrorResumeNext statement that causes the script interpreter to ignore runtime errors and continue execution of the script code. Then the script can check the value of the Err.Number property to determine whether an error occurred. If an error occurs, a non-zero value is returned