Writing Action Queries and Stored Procedures

Data Manipulation Language (DML) commands are implemented by action queries: append, update, delete, and make-table. Jet uses QueryDefs for action queries; ADP require stored procedures or functions to support INSERT, UPDATE, DELETE, and SELECT...INTO statements. This section shows the standard syntax for each type of action query, which is identical in Jet SQL and T-SQL.

Append queries use the following syntax:

 INSERT INTO dest_table    SELECT [ALL|DISTINCT] select_list    FROM source_table   [WHERE append_criteria] 

If you omit the WHERE clause, specified field values of all records of source_table are appended to dest_table. The source_table must exist and have columns that correspond to those you specify in select_list.

For an example of a stored procedure that appends records, see "Adding Records with Append Stored Procedures," p. 831.

UPDATE queries use the SET command to assign values to individual columns:

 UPDATE table_name    SET column_name = value [, column_name = value[,...]]    [WHERE update_criteria] 

Separate multiple column_name = value entries by commas if you want to update the data in more than one field. If you omit the WHERE clause, the SET expression acts on every record of the table, which probably isn't your intention.

Jet SQL and T-SQL support the ANSI SQL VALUES keyword for adding records to or updating tables the hard way (specifying the value of each column of each record). The later "Taking Advantage of Transactions in Stored Procedures" section has a T-SQL statement that uses the VALUES approach.

DELETE queries take the following form:

 DELETE FROM table_name    [WHERE delete_criteria] 

If you omit the optional WHERE clause in a DELETE query, you delete all records from table_name.

To review creating DELETE stored procedures in the project designer, see "Deleting Table Records," p. 834.

Make-table queries use the following syntax:

 SELECT [ALL|DISTINCT] select_list    INTO new_table    FROM source_table   [WHERE append_criteria] 

To copy the original table, substitute an asterisk (*) for select_list and omit the optional WHERE clause. Data types and sizes in the new_table are the same as those of the source_table. If your SQL Server source_table has a computed column, the corresponding column in the new_table contains the computed values, not the expression used to compute the column.

For an example of a simple make-table stored procedure, see "Creating and Executing Make-Table and Update Stored Procedures" p. 828.

You can execute any of the preceding SQL statements directly against Jet or SQL Server tables from VBA code and an ActiveX Data Objects (ADO) Connection object. Using VBA code to execute SQL statements directly, rather than designing stored procedures for action queries or views and functions to return Recordsets is an option for ADP. Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO," provides examples of generating and executing SQL statements with VBA code.

Specifying Parameters for Criteria and Update Values

When you design Jet queries or stored procedures to update table data, you specify parameters to supply values to WHERE clause criteria and, for INSERT and UPDATE queries, field values. If you specify the data type for input parameters, Jet adds a PARAMETERS declaration that precedes the SQL statement. The sequence of the parameter values in the declaration determines the order in which the Enter Parameter Value dialogs appear.

Jet SQL

The Jet SQL statement for a typical Jet parameterized UPDATE query for the quantity field of the Order Details table is

 PARAMETERS [Type Order Number] Long,            [Type Product Code] Long,            [Type New Quantity] Short; UPDATE [Order Details]    SET [Order Details].Quantity =        [Enter New Quantity]    WHERE [Order Details].OrderID =             [Type Order Number] AND          [Order Details].ProductID =          [Type Product Code]; 

The semicolon at the end of the PARAMETERS declaration indicates the start of an independent SQL statement.

T-SQL uses variables to store parameter values; SQL Server identifies user variables by an @ prefix. T-SQL supports input and output parameters, and return values; this section deals only with input parameters, which are by far the most common type.

T-SQL

Following is an example of standard T-SQL syntax to create a parameterized UPDATE stored procedure:

 CREATE PROCEDURE tsql_edititems   (@OrderID int,    @ProductID int,    @Quantity smallint) AS UPDATE [Order Details] SET Quantity = @Quantity WHERE OrderID = @OrderID AND    ProductID = @ProductID 

Parentheses surrounding the three parameter declarations are optional.

If you type the entire statement into the project designer's SQL pane, you receive an error when you click Verify SQL Syntax. The designer wants only the UPDATE statement with the variables. To avoid the error message, open the New Query dialog, choose Create Text Stored Procedure, and replace the SQL window's skeleton CREATE PROCEDURE statement with the preceding code.

The right side of the SET expression, = @Quantity, appears in the New Value cell of the Quantity row of the project designer's grid. The WHERE clause variables appear as = @OrderID and = @ProductID in the Criteria column. Unlike Jet parameters, you can't change the sequence of appearance of the Enter Parameter Value dialogs by rearranging the rows of the grid; the dialog to enter the UPDATE variable always opens first.

Note

When you execute the preceding UPDATE query, you receive a "The stored procedure executed successfully, but did not return records" message no matter what values you type in the three parameter dialogs (including nothing). Stored procedures that update tables should include T-SQL code to test for parameter value entry errors, especially when you execute them interactively. The "Programming Stored Procedures" topic of SQL Server Books Online has an example of a test for a missing (NULL) parameter value. If you don't clear the Confirm Action Queries check box on the Option dialog's Edit/Find page, Jet action queries display the number of rows that will be affected by execution. The Confirm Record Changes check box values don't apply to execution of T-SQL stored procedures or functions.

Taking Advantage of Transactions in Stored Procedures

Unless you specify otherwise, Jet automatically uses transaction processing when updating data in multiple tables with a single SQL statement. SQL Server requires explicit declaration of the beginning of the transaction and its end. When you write a T-SQL statement that makes changes to more than one table, wrap the statement with BEGIN TRAN[SACTION] and COMMIT TRAN[SACTION] statements, as illustrated by the following simple example.

T-SQL

This simple DELETE stored procedure uses a transaction to assure that the Order Details table doesn't end up with orphan records after deleting the parent Orders record:

 CREATE PROCEDURE tsql_delorder    @OrderID int AS BEGIN TRAN    DELETE FROM [Order Details]       WHERE OrderID = @OrderID    DELETE FROM Orders       WHERE OrderID = @OrderID COMMIT TRAN 

If you want to create the preceding stored procedure, choose Create Text Stored Procedure (not Design Stored Procedure) in the New Query dialog.

The following stored procedure is one of several that were used to create the NwindXL19.mdb files for Chapter 19, "Linking Access Front-Ends to Jet and Client/Server Tables." The AddOrders.adp application adds four stored procedures to a modified copy of the NorthwindCS database, including the preceding example. The only modification to the copy of the database is removal of the IDENTITY attribute from the OrderID field of the Orders table to permit adding and deleting records while adding records that have consecutive order numbers. Instead of an identity column, the procedure checks for the last OrderID value and adds 1 for the new order number.

T-SQL

This sample stored procedures adds a new order to an Orders table (which doesn't have an identity field), and then adds the first line item for the order to the Order Details table:

 CREATE PROCEDURE tsql_addorder    @CustID varchar(5),    @EmpID int,    @OrdDate datetime,    @ReqDate datetime,    @ShipVia int,    @ShipName varchar(40),    @ShipAddr varchar(60),    @ShipCity varchar(15),    @ShipReg varchar(15) = NULL,    @ShipZIP varchar(10) = NULL,    @ShipCtry varchar(15),    @ProdID int,    @Price money,    @Quan int,    @Disc real AS DECLARE @OrderID int SET NOCOUNT ON BEGIN TRAN    SELECT @OrderID = MAX(OrderID) FROM Orders    SELECT @OrderID = @OrderID + 1         INSERT Orders            VALUES(@OrderID, @CustID, @EmpID,              @OrdDate, @ReqDate, NULL, @ShipVia,              NULL, @ShipName, @ShipAddr, @ShipCity,              @ShipReg, @ShipZIP, @ShipCtry)         INSERT [Order Details]            (OrderID, ProductID, UnitPrice, Quantity, Discount)            VALUES(@OrderID, @ProdID, @Price, @Quan, @Disc) COMMIT TRAN SET NOCOUNT OFF IF @@error = 0    RETURN @OrderID ELSE    RETURN 0 

Don't even think about typing this or similar stored procedures into the SQL pane of the project designer. The designer can't handle even moderately complex stored procedures. Instead, type the stored procedures in the windows opened by the Create Text Stored Procedure selection in the project designer's New Query dialog.

Following are brief descriptions of the new T-SQL elements in the preceding example:

• The @ShipReg varchar(15) = NULL and @ShipZIP varchar(10) = NULL parameter declarations have default NULL values to accommodate addresses that don't have a ShipRegion or PostalCode value. Unlike Jet parameters, you can assign default values to T-SQL parameters.

• The DECLARE @OrderID int statement creates an internal @OrderID variable to return the order number to the VBA subprocedure that calls the stored procedure and supplies the parameter values.

• The two SELECT statements obtain the last OrderID value, and add 1 to specify the new value.

• SET NOCOUNT ON eliminates a roundtrip to the server to report the number of records affected.

• The two INSERT statements illustrate different methods of using the VALUES function. The first example doesn't include a field list, so the comma-separate list of values must correspond to the sequence of fields in the table. The second example includes a field list that defines the sequence for the following VALUES list.

• SET NOCOUNT OFF is optional, but is included here as a good stored procedure programming practice (GSPPP).

• The IF @@error conditional statement assigns the new @OrderID value to the RETURN value if the transaction commits (@@error = 0). @@error is an SQL Server system variable that returns a nonzero value when an execution error is encountered. The RETURN value is 0 if the transaction fails. Error handling is one of the primary uses for T-SQL's flow-control structures.

This section's example appears complex, but it pales in comparison with stored procedures and SQL scripts that are used in production databases. For an example of a truly complex script, make a copy of ...\Office10\Samples\NorthwindCS.sql as NorthwindCS.txt, and then open it in Notepad. Go to the end of the file, and then Page Up to view the INSERT statements that Microsoft generated to populate the Suppliers and Products tables.

Note

Use the NorthwindCS.txt copy to prevent making inadvertent changes to NorthwindCS.sql. Much of NorthwindCS.txt's 670KB is devoted to binary data for the bitmaps stored in the OLE Object field of the Categories table. Access 2000's NorthwindCS.sql was even larger (1.4MB), because it included data for the larger bitmaps of the Employees table's Photo field.