Examining Stored Procedures

graphics/2002_icon.gif

Access 2003's project designer lets you create many common types of stored procedures without writing any T-SQL code. This new feature provides parity between Jet and SQL Server action queries, because you can create make-table, update, append values, and delete queries in the designer, instead of writing T-SQL in the text editor. (Access 2000's project designer opened the text editor for new and existing stored procedures.) Like Jet UNION queries, you must write SQL Server UNION queries for views, functions, and stored procedures in the text editor. The project designer doesn't have a Query, SQL Specific, Union menu choice.

graphics/queries_page.gif NorthwindCS includes only parameterized SELECT stored procedures, which you create using the same process as that for parameterized in-line functions. Open one or more of the sample stored procedures in Design view to see the similarity between the grid and T-SQL statements for stored procedures. Simple parameterized in-line functions are updatable in Datasheet view; stored procedures parameterized or not aren't updatable.

Creating and Executing Make-Table and Update Stored Procedures

To give the project designer a test run with make-table and update queries, do this:

  1. graphics/query_design_window.gif Click New in Database window's Queries page to open the New Query dialog, and double-click the Design Stored Procedure item.

  2. graphics/sql_pane.gif Double-click the Orders item in the Add Table dialog, close the dialog, and click the SQL button. By default, a SELECT query skeleton statement opens in the SQL pane. Mark the * (All Columns) check box.

  3. graphics/make_table.gif Choose Query, Make-Table Query or click the Query Type button, and choose Make-Table Query to open the Make Table dialog. Type a table name, such as tblOrders, in the text box, and click OK to create a SELECT dbo.Orders.* INTO dbo.tblOrders FROM dbo.Orders statement (see Figure 20.25).

    Figure 20.25. The process of creating a make-table stored procedure is identical to that for creating a Jet make-table query.

    graphics/20fig25.gif

    Note

    It's an SQL Server convention to use two-letter object type prefixes for views (vw), stored procedures (sp or xp), and functions (fn). Microsoft and many developers add an underscore, especially when naming objects with the even more traditional all lowercase convention. However, adding an underscore to the sp or xp prefixes conflicts with SQL Server's use of sp_ for system stored procedures and xp_ for extended stored procedures. It's not common to prefix table names with tb or tb_, but tmp or tmp_ often precedes temporary table names. The examples of this chapter use the tbl prefix for consistency with the Jet tables you created in earlier chapters.

  4. graphics/run_toolbar.gif Click the Run button, and save your stored procedure as spMakeTable. After SQL Server stores and executes the stored procedure, a message confirms that the stored procedure executed, but didn't return any records. Click OK, and then close the project designer.

    Note

    graphics/primary_key.gif

    The new tblOrders table you created has a structure that's identical to the Orders source table, including the Identity property of the OrderID field. Make-table queries don't set a primary-key field, so the tblOrder table isn't updatable in Datasheet view. (SQL Server tables require a primary key for the cursor-based updates used by datasheets, but not for updates by stored procedures.)

    To add a primary-key field, open tblOrders in Design view, select the OrderID field, click the Primary Key button on the toolbar, close the table, and save the changes.

  5. Repeat steps 1 and 2, but use the tblOrders table you just created to avoid making changes to the sample Orders table.

  6. graphics/action_queries.gif Choose Query, Update Query to change the grid and skeleton UPDATE dbo.tblOrders SET statement to the make-table syntax.

  7. Mark the OrderDate, RequiredDate, and ShippedDate check boxes of the field list to add the fields to the SET statement, which defines the fields to be updated and their new values. Pencil icons in the check boxes indicate a pending UPDATE operation.

  8. Type OrderDate + 365, RequiredDate + 365, and ShippedDate + 365 in each column's corresponding New Value cell to add a year to date values. Type not null in the Criteria cell of the ShippedDate row to prevent an attempt to add a year to a NULL value. The designer changes the entry to NOT IS NULL, and adds the WHERE NOT ShippedDate IS NULL criterion (see Figure 20.26).

    Figure 20.26. Stored procedures that update table values also follow Jet's methodology.

    graphics/20fig26.gif

    Note

    Like the Jet query designer, the project designer adds multiple parenthesis pairs to the WHERE clause. Parentheses aren't required for simple criterion, but are needed to specify the application sequence of operators such as AND, OR, and NOT in complex WHERE clauses.

  9. graphics/sql_statment.gif graphics/run_toolbar.gif Click the Verify SQL Syntax button, click OK to dismiss the syntax confirmation message, and click the Run button. Save the stored procedure as spUpdateOrders. Click OK to dismiss the execution confirmation message, and close the project designer.

  10. Open tblOrders from the Database window, not by changing to Datasheet view of the designer, to verify the updates. Changing to Datasheet view executes the stored procedure again, which adds another year to the dates.

Caution

Jet UPDATE queries post a warning message that indicates the number of rows to be changed, if you haven't cleared the Confirm Record Changes check box of the Option dialog's Edit/Find page. You receive no warning when you execute a stored procedure that updates tables.


Note

The IS NOT NULL criterion appears on the same line as ShippedDate, which implies that the criterion only applies to that column and that the query would update the OrderDate and RequiredDate columns for orders with missing ShippedDate values. This is not the case. The criterion applies to all columns included in the SET statement. To update all dates, you need to run two queries one for OrderDate and RequiredDate and one for ShippedDate.


Adding Records with Append Stored Procedures

To execute an append (INSERT INTO) stored procedure you need a table with the same structure as the source table. For example, if you use tblOrders as the source table, you need a new empty table with the same structure as tblOrders. Create a new copy of the Orders (not the tblOrders) table by selecting Order, pressing Ctrl+C, Ctrl+V, typing tblOrdersEmpty, and selecting Structure Only in the Paste Table dialog.

Dealing with Identity Fields

The tblOrders and tblOrdersEmpty tables have a field of the int data type with the Identity property set to Yes, which is equivalent to a Jet AutoNumber field. You have the following choices when appending new records to a table that has an Identity field:

  • Change the Identity property value in the target table to No. This approach lets you append records with the source table's value in the former identity column. (You can use SELECT * FROM SourceTable in the SELECT INTO statement.) After you append the records, you can set the Identity property to Yes. You don't need to change the Identity Seed property value from the default (1).

  • Change the Identity Seed value to the same starting number as the records you're appending (10248 for tblOrders), or to another value to renumber the records consecutively. In this case, you specify all except the identity field in the SELECT field list.

Note

It's much easier to change the identity starting value for an SQL Server table than the initial value of a Jet AutoNumber field.


Follow these steps to prepare for testing the second of the preceding choices:

  1. graphics/design_view.gif Open the tblOrdersEmpty table in Design view, select the OrderID column, and change the Identity Seed value to any number you want. This example uses 1010248, which represents a change to increase the number of digits for order numbering without renumbering the significant five digits of older orders. (Unfortunately, you can't use 010248, because the leading zero is stripped when you save the table design.)

  2. graphics/current_record.gif Click Table Datasheet view, save your design changes, and verify that tblOrdersEmpty displays the (AutoNumber) default in the only (empty) record.

  3. Close the table. If you leave the table open, the appended records don't appear until you close and reopen it.

Creating the Append Query

To append and renumber records from the tblOrders table, do the following:

  1. graphics/query_design_window.gif graphics/sql_pane.gif Click New in the Database window's Queries page, double-click Design Stored Procedure, add tblOrders to the default SELECT query, and click the SQL button.

  2. graphics/append.gif Mark each field name check box for the CustomerID through the ShipName fields but omit ShippedDate and Freight, and then choose Query, Append Query to open the Choose Target Table for Insert Results dialog (see Figure 20.27).

    Figure 20.27. You must specify the target table for append queries. The Choose Target Table for Insert Results dialog is a redesigned version of Jet's Append dialog.

    graphics/20fig27.jpg

  3. Select the tblOrdersEmpty table in the list, and click OK to change the grid layout to the append query format, and add the INSERT INTO TableName ( FieldList) statement to the query (see Figure 20.28). The check box icons change to plus (+) signs.

    Figure 20.28. The grid for an append query has columns corresponding to the Jet append query design grid's rows.

    graphics/20fig28.gif

  4. graphics/running_query.gif Click Run, save your stored procedure as spAppendOrders, and acknowledge the execution confirmation message.

  5. Open the table with the appended records to confirm the renumbering process (see Figure 20.29).

    Figure 20.29. The appended records have one million added to the original order number.

    graphics/20fig29.jpg

graphics/query_append_value.gif Stored procedures have an append values query option that Jet doesn't offer. An append values query lets you add one record at a time (perform an INSERT) with a stored procedure. Practically speaking, append values queries are only useful as parameterized stored procedures; in most cases, you supply the parameter values with VBA code.

To test-drive an append values query with the tblOrdersEmpty table, do this:

  1. graphics/query_design_window.gif Close the table, open a new stored procedure, and add the tblOrdersEmpty table to it.

  2. Mark the check boxes for the fields, except the OrderID field, that are valid when entering an order. ShippedDate and Freight, for example, don't receive values until the order goes out the door, assuming that your organization complies with generally accepted accounting practices (GAAPs).

  3. graphics/query_append_value.gif Choose Query, Append Values Query to change the grid to a Column and Value list.

  4. Type appropriate values in each of the Value cells. You must type a value into each cell; use NULL to specify a value that's unknown or not applicable. Character identifiers are added automatically to ...char and datetime fields. Typing the values adds them to the query's VALUES list, with a CONVERT function added for datetime fields (see Figure 20.30).

    Figure 20.30. Append value stored procedures let you add a single record with values you type in the grid. Without parameters, stored procedures of this type aren't very useful.

    graphics/20fig30.gif

  5. graphics/running_query.gif Click Run, save your stored procedure as spAppendBogusOrder or the like, acknowledge the confirmation message, close the procedure, and open the table to verify the appended order.

Deleting Table Records

Delete queries saved as stored procedures without parameters aren't much more useful than append value queries. To create a simple parameter query to delete older records from the tblOrdersEmpty or tblOrders table, run the following drill:

  1. graphics/query_design_window.gif Create a new stored procedure, and add tblOrdersEmpty to it.

  2. graphics/delete.gif Choose Query, Delete Query to set up the grid, add a skeleton DELETE FROM TableName statement, and change the * (All Columns) item's icon to the delete symbol.

  3. Drag the column(s) on which to establish deletion criterion ShippedDate for this example to the grid.

  4. Type the parameter criterion, <=@Delete_Date, in the Criteria column.

  5. ShippedDate is NULL for some orders, so drag a second copy of ShippedDate to the grid, and type NOT IS NULL in the Criteria column. The stored procedure design appears as shown in Figure 20.31.

    Figure 20.31. The query for this stored procedure that deletes older records requires compound criteria.

    graphics/20fig31.gif

  6. graphics/running_query.gif Click Run, save the stored procedure as spDeleteParam or similar, and type a date that's valid for the table to delete records for shipments on or before the date.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net