Share this Page URL

Chapter 14. Understanding Access Project... > Working with Views and Stored Proced... - Pg. 533

Understanding Access Projects 533 Stored Procedures A stored procedure is a collection of SQL statements, compiled and stored on a SQL Server data- base, that can be processed as a single unit. A stored procedure, like an event procedure, can contain statements that control the logical flow of a database operation. For example, when one field is updated in a record, another must be updated as well. A stored procedure can contain pa- rameters and can return a single or more than one set of records. Also, because stored procedures are compiled on a server, they execute more quickly than individual SQL statements. For the most part, you can't update records that are returned by a stored procedure. However, if a user has the appropriate permissions for the tables a stored procedure is based on, you can add, delete, and update records with a form that uses a stored procedure as its record source. In the following steps, we'll create a simple stored procedure. Create a stored procedure 1. 2. In the Database window, click Queries in the Objects list, and then Double-click Create Stored Procedure In Designer. In the Add Table dialog box, hold down the Ctrl key and select the MarketingCampaigns and Tasks tables. Click Add, and then click Close. The Query Design window should now look like the following: 3. 4. 5. In the field list for the MarketingCampaigns table, select the Country check box. In the field list for the Tasks table, select the check boxes for the fields TaskName, Priority, StartDate, and DueDate. In the Criteria column for the Country field, enter =@Enter_Country. In the Criteria column for the Priority field, enter =@1. The stored procedure should now look like the following: