Programming | Advanced 9.2.3 stored Procedures
The use of stored procedures is an area where command objects are applied. Stored procedures, sometimes called store queries, are predefined SQL query statements stored in the database.
Why should you create and use stored procedures instead of using SQL strings directly in your code? There are several main reasons for this:
· Stored procedures have been compiled by the database. This produces an "execution plan," so the database knows exactly what it will do, which speeds up the process.
· Stored procedures are typically cached by the database, which makes them run faster because they do not need to be read from disk at this time. Not all databases support this caching mechanism, as Microsoft Access does not, while SQL Server supports it.
· You can ensure that the data is more secure by specifying that the tables in the database can only be modified by stored procedures. This means that a potentially dangerous SQL operation is not performed.
· You can avoid mixing ASP code with lengthy SQL statements to make your ASP code easier to maintain.
· You can centralize all your SQL code on the server.
· You can use an output parameter in a stored procedure to allow you to return a recordset or other value.
Generally, stored procedures are almost always faster than equivalent SQL statements.
In order to use a stored procedure, the name of the stored procedure is used as the command text and the appropriate type is set. For example, consider the previous example of updating a 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 Access databases, you can use a simple update query statement to accomplish the same task, as shown in Figure 9-1:
Figure 9-1 Using Microsoft's Access database to complete the update query
To run the stored procedure in an ASP Web page, you only need to 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 the stored procedure. No recordset is returned because it is only updating the data. Keep in mind that you do not create a recordset unless you really need it.
While this can be done, it is not very flexible, as it deals with only one type of book. A better approach would be to create a process that allows us to choose the type of book, so we don't have to create a process for each kind of encyclopedia. You can also remove fixed 10% updates, which makes flexibility even better. So, how do you do this, very simply, using parameters.
1. Parameters
The parameters (or variables) of a stored procedure, like the general procedure and function parameters, can be passed inside the function, and the function can use its value. Stored procedures in SQL Server (as in other databases, including access) have this functionality.
In order for a stored procedure to handle multiple types of books, or even allow the user to specify a price increase (or decrease), additional parameters are required:
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 comes with two parameters:
· One is the type of book (@Type).
· One is the percentage of book price Changes (@Percent).
Like VBScript functions, these parameters are variables. However, unlike VBScript and other scripting languages, variables in these scripting languages are variant types, while SQL variables have certain types (char, money, and so on). The naming convention for SQL variables must be followed, that is, the variable must start with a symbol @.
Notice that we have the percent as an integer (for example, 10 for 10%), not as a fraction of the value passed into this procedure. This just makes the stored procedure more intuitive.
2. Parameters Collection
So now you have a stored procedure with parameters, but how do you call it through ADO? We've seen how to use command objects to invoke stored procedures without parameters, and in fact, there is no difference between them. The difference is in the use of parameters collections.
The Parameters collection contains the parameter object for each parameter in the stored procedure. However, ADO does not automatically know what these parameters are, so you must create them using the CreateParameter method in the following form:
Set Parameter = Command.createparameter (Name, [Type], [Direction], [Size], [Value])
The parameters and instructions are shown in table 9-3:
Parameter and description of table 9-3 CreateParameter method
Parameters
Description
Name
The name of the parameter. This is the name of the parameter in the Parameters collection, not the parameter name in the stored procedure. However, the use of the same name is a good practice
Type
The data type of the parameter. Can be a Addatatype constant, as detailed in the appendix
Direction
The direction of the parameter, indicating whether the parameter provides information to the stored procedure, or whether the stored procedure returns information to ADO. Can be one of the following values:
adParamInput, parameter is the input parameter passed to the stored procedure
adParamOutput, parameter is the output parameter retrieved from the stored procedure
adParamInputOutput, parameters can be used as input and output parameters at the same time
adParamReturnValue, this parameter contains the state returned by the stored procedure
Size
The length of the parameter. For fixed-length types, such as integers, this value can be ignored
Value
The value of the parameter
Once you have created a parameter, you can append it to the Parameters collection, for example:
Set Parvalue = Cmdupdate.createparameter ("@Type", adVarWChar, adParamInput, _
, "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 hold parameters, and the default variant type can already work pretty well. If you do not 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 using the Append method. This method runs faster than using a variable, but adds a line of code that is less readable. You can choose one of these methods according to your hobby.
parameter to the Parameters collection, it is persisted, so it is not necessarily necessary to assign a value to each parameter when the parameter is created. You can set the value of a parameter at any time before the command is run. For example:
CmdUpdate.Parameters.Append = _
Cmdupdate.createparameter ("@Percent", Adcurrency, adParamInput)
Cmdupdate.parameters ("@Percent") = 10
The previous chapter mentions several ways to access values in a collection, and the Parameters collection is no different. The example above uses the name of the parameter to retrieve the parameter in the collection, or it can be retrieved using the index number:
Cmdupdate.parameters (0) = 10
The above code assigns the first (Parameters collection from 0 numbering) argument in the parameter collection
[1] [2] [3] Next page
