Understanding Views, Stored Procedures, and User-Defined Functions

3 4

The Queries tab for a project lists views, stored procedures, and user-defined functions. You'll use views to filter and sort data, stored procedures to modify data, and user-defined functions to combine various features of views and other queries into a single query that accepts parameters and returns values. These project components are described in the following sections.

note


Views, stored procedures and user-defined functions in projects are generically called queries.

Project Views

A project view is similar to an Access select query in that it selects columns from one or more tables and possibly applies a filter to limit the rows in the view. Unlike Access queries, however, project views can't have parameters. See Chapter 11, "Working with Advanced Queries," for a detailed discussion of parameter queries. Like Totals queries, views can also summarize data. Views are updatable if the user has the appropriate permissions on the underlying table(s).

InsideOut

Don't confuse project views with views in Access databases, such as Design view, Datasheet view, and Form view for forms and Design view and Print Preview for reports. Project views are an entirely different type of object, and it might have been better if they had been given a different name .

Here's an example of the confusion: When you create a new form in an Access database, one of the choices in the New Form dialog box is Design View, which lets you create a new form in Design view. But when you're working in a project, creating a new query, the Design View option in the New Query dialog box lets you design a new view.

To create a view, you can either double-click the Create View In Designer item in the Database window or click New and then select Design View in the New Query dialog box, as shown in Figure 19-26.

figure 19-26. you can create a new project view using the new query dialog box.

Figure 19-26. You can create a new project view using the New Query dialog box.

In either case, a blank Query Designer window will appear, with the Add Table dialog box open over it. You can select one or more tables, views, or functions to add to the diagram pane of the Query Designer, as shown in Figure 19-27.

figure 19-27. you can add tables and other objects to a view using the add table dialog box.

Figure 19-27. You can add tables and other objects to a view using the Add Table dialog box.

When you add tables to the Query designer's Diagram pane (the top pane), any existing links between the tables will be displayed, and you can create new links by clicking a field in one table and then dragging it to the matching field in another table. As with Access queries, links created in a view apply only to the view in which they're created.

The Query Designer interface (which is more sophisticated than the analogous Access Query Designer) displays tables in the Diagram pane and lists the selected fields from the tables in the Grid pane (the bottom pane). The table field lists have check boxes for each field; selecting the check box next to the field adds it to the view and places it on the Grid pane. If you place your mouse pointer over the join between tables, the join type will be displayed in a ScreenTip (also called a ToolTip). Figure 19-28 shows a view with a ScreenTip in the Publications project.

figure 19-28. you can see join details in a project view that's open with all three panes displayed.

Figure 19-28. You can see join details in a project view that's open with all three panes displayed.

The Query Designer has three panes: the Diagram pane, the Grid pane, and the SQL pane. You can have one, two, or all three panes open at once. The Diagram pane shows the tables, views, or inline functions you're querying; the Grid pane lets you set options for the data columns used in the query; and the SQL pane displays the query's SQL statement. (We'll examine in-line functions in the "User-Defined Functions" section,.) Figure 19-29 shows a view in the Publications project, with all three panes visible.

figure 19-29. a publications view can have all three panes open in the query designer.

Figure 19-29. A Publications view can have all three panes open in the Query Designer.

To open another pane in the Query Designer, click its corresponding button on the Query Designer toolbar, as shown in Figure 19-30.

figure 19-30. the query designer toolbar has buttons for opening the diagram, grid, and sql panes.

Figure 19-30. The Query Designer toolbar has buttons for opening the Diagram, Grid, and SQL panes.

Stored Procedures

Stored procedures are a cross between queries and procedures. Unlike views, stored procedures can have parameters, and they can handle more complex logic than views. Generally, stored procedures are not updatable, but Access lets you add, delete, and update records on a form bound to a stored procedure if the user has Add, Update, and Delete permissions on the underlying tables.

To create a new stored procedure, double-click the Create Stored Procedure In Designer item in the Database window, or click New and then select Design Stored Procedure in the New Query dialog box, as shown in Figure 19-31.

figure 19-31. you can create a new stored procedure using the new query dialog box.

Figure 19-31. You can create a new stored procedure using the New Query dialog box.

In either case, you will get a blank Query Designer window, with the Add Table dialog box open over it. You can select one or more tables, views, or functions to add to the Diagram pane of the Query Designer. Figure 19-32 shows a NorthwindCS stored procedure opened in the Query Designer, with all three panes visible.

figure 19-32. you can view a northwindcs stored procedure in the query designer.

Figure 19-32. You can view a NorthwindCS stored procedure in the Query Designer.

Stored procedures are more powerful than views. They can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values. You can use a stored procedure for any purpose that you can use a SQL statement for, but with these additional advantages:

  • A series of SQL statements can be executed in a single stored procedure.
  • Other stored procedures can be referenced within a stored procedure.
  • Stored procedures are compiled on the server when they're created, so they run faster than individual SQL statements.

note


For more information about writing stored procedures, you might want to look at a book that delves into SQL Server in depth. A good book to check out is Inside Microsoft SQL Server 2000 by Kalen Delany Based on the first edition by Ron Soukup (Microsoft Press, 2000).

User-Defined Functions

User-defined functions combine features of views and queries into a single query that you can pass parameters to, sort, and use to return values. They can be used as record sources for forms, reports, or combo boxes, but not for data access pages. There are three types of user-defined functions, all of which you can graphically create and edit using the Query Designer.

  • In-line function. Contains a single SELECT statement and returns an updatable table of data. Can be used in the FROM clause of a query.
  • Table-valued function. Contains one or more SELECT statements and returns a nonupdatable table of data. Can be used in the FROM clause of a query.
  • Scalar function. Contains one or more SELECT statements and returns a scalar value, such as the int, decimal, varchar, sql_variant, or table data type. You can use a scalar function anywhere in a query that you can use a column name.

Parameter Queries

A parameter query has a placeholder for a value that's supplied when the query is run. To turn the reptq1 stored procedure in the Publications project into a parameter query, open the stored procedure in Design view, and type the following expression into the Criteria cell of the pub_id column in the Grid pane of the query:

 = @Enter_pubid 

When the query is run, you'll see a dialog box in which you can type the pub_id value, as shown in Figure 19-33.

figure 19-33. a parameter query opens this dialog box.

Figure 19-33. A parameter query opens this dialog box.

After you enter a value, click OK. The query will open in Datasheet view, showing only records with the matching pub_id value, as shown in Figure 19-34.

figure 19-34. a stored procedure is filtered by a parameter.

Figure 19-34. A stored procedure is filtered by a parameter.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

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