Invoking Data-Modification Stored Procedures
Another good use of stored procedures is to perform data modifications. When you let stored procedures be the data gatekeepers, you enhance security and maintainability. Again, the primary benefit of stored procedures is that the client code, which runs on a user's workstation or a distributed server, doesn't have to generate SQL or understand the database schema.
ADO Command objects do a good job of supporting the client code running on a user's workstation or a distributed server because you can use Command objects to execute stored procedures, with parameters to perform updates, inserts, and deletes. Rather than creating updateable cursors in ADO, you're better off executing stored procedures with parameters to perform the modification, especially when you're using SQL Server, because you can precisely control the timing and nature of the updates. Listing 5, page 42, contains the code to implement an AddOrderDetail procedure, which you can use to add an order detail record to the database when you call the stored procedure usp_AddOrderDetail. Note that in Listing 5, the Execute method of the Command object passes as a reference to the local variable lngAffected with the constant adExecuteNoRecords. The variable lngAffected passes by reference to the Execute method and ADO subsequently populates it with the number of rows the command affected. In Listing 5, the lngAffected variable specifies the number of rows ADO inserts into the Order Details table. The constant adExecuteNoRecords saves memory by instructing ADO not to create a Recordset object when the command executes.
Command objects that contain multiple parameters, such as the command in Listing 5, are good candidates for declaring in module-level variables. Caching Command objects eliminates the overhead in reconstructing the Parameters collection with each invocation, at the cost of using extra memory.
In Listing 6, the T-SQL stored procedure usp_AddOrderDetail performs the insert into the Order Details table and checks the data to ensure that the quantity of items ordered is greater than zero. The procedure uses the RAISERROR statement to generate an error message, which is sent back to the client. The RAISERROR statement accepts arguments including the message, severity, and invocation state of the error. For frequently used messages, the statement can also accept a system- or user-defined message ID for retrieving the error message from the systemessages table. In VB, RAISERROR triggers the active error handler and populates both the intrinsic Err object and the Errors collection of the ADO Connection object.
Invoking Stored Procedures That Return
Values
Output parameters and returned values are information other than a result set, which the stored procedure returns. The stored procedure handles both output parameters and returned values through the Command object's Parameters collection.
Returning Output Parameters. To help understand output parameters, see the T-SQL stored procedure in Listing 7. The stored procedure usp_Get-RevByWeek calculates the total amount of revenue and the number of orders placed for a rolling seven-day period; it calculates from the start date, which is passed in as an input parameter. You perform the revenue calculation when you apply the SUM aggregate function to the Order Details table, which contains each product in each order, and multiply the unit price by the order's quantity. Similarly, you calculate the total number of orders by counting the distinct order ID numbers in the Orders table with the COUNT aggregate function. This stored procedure doesn't return a result set; it returns two output parameters: revenue amount and number of orders considered.
As I noted previously, the Parameters collection handles output parameters, which are analogous to arguments you pass by reference in other programming languages. Listing 8 shows the code to call the usp_GetRevByWeek stored procedure.
You also execute the usp_GetRevByWeek stored procedure in Listing 8 with the adExecuteNoRecords constant because only parameters, and not a result set, return. The usp_GetRevByWeek lets ADO bypass creating a Recordset object for the Command. The main difference between using output parameters and using input parameters is that you use the adParamOutput constant in the CreateParameter method to create the parameters. After the usp_GetRevByWeek is executed, you can use the Parameters collection to read the parameters and reference them by name or ordinal number. In this case, the VB procedure passes the output parameters by reference back to the calling code. If a stored procedure returns a single-row, single-column result set or a single row with only two columns, it's more efficient to return those results in output parameters than as a Recordset object. Returning results as output parameters is efficient because ADO doesn't need to build and maintain a Recordset object on the client.
Return Values. The other type of value that a stored procedure can return is the return value. In SQL Server, for example, all stored procedures return a T-SQL integer identifying the execution status of the procedure. Also, you can use the return value to return data that the client application requires.
A typical use of return values is in self-incrementing identity columns, which you define with a seed value (the value of the first row loaded into the table) and increment values. Keep in mind that each table can contain only one identity column, which SQL Server automatically populates each time it inserts a row. You can use these columns to assign a system-generated unique number to a row in the table. Generally, you use this column as the primary key of the table when a natural key isn't present or when the natural key might require future updates. Listing 9, which you can download at http://www.sqlmag.com at the link to this article, shows how to use an identity column in the usp_AddOrder stored procedure. Here, the primary key of the Orders table is OrderId, an identity column that SQL Server automatically populates when the INSERT statement is executed. After the stored procedure performs an INSERT, the RETURN statement passes back the global variable @@identity. SQL Server populates this global variable, which represents the last identity value it inserts during this connection. If you use the technique in Listing 9, the stored procedure always returns the primary key of the new row representing the new order, which the client program needs to manipulate the order by its primary key value.
To handle the return value, you create a parameter with the constant adParamReturnValue in the first position (ordinal 0) of the Parameters collection. When you use the Refresh method of the Parameters collection, the method adds the return value automatically to the collection as the first element. Listing 10, which you can download at http://www.sqlmag.com at the link to this article, shows the AddOrder procedure and illustrates the technique of reading the return value. This procedure simplifies the process of adding an order by requiring that only two values are inserted into the order-customer ID and ship name.
As you can see, you need to follow only a few rules to use stored procedures effectively with ADO. I hope this brief tutorial will encourage you to use stored procedures to get the most out of your SQL Server and ADO applications.
End of Article
Prev. page
1
[2]
next page -->
