Views and Stored Procedures

Although SQL Server does not offer queries for building custom applications, as Access does, it provides similar functionality in its views and stored procedures. A view is a SQL statement that returns rows without using parameters or an ORDER BY phrase. In contrast, a stored procedure can use both parameters and the ORDER BY phrase to specify a return a row set. Stored procedures can also perform SQL statements that do not return rows, such as INSERT, UPDATE, and DELETE.

Using Views

While the rules for writing queries change as you move from Jet to MSDE or SQL Server, the new Access Query Designer should help to ease the transition. It resembles the query designer for Jet-based queries. You can use the Query Designer to build views. (You have to write stored procedures without the help of a Builder wizard.) It consists of three panels that you can turn on or off. You can build a view in any panel. Entries in one panel automatically update the other two.

Figure 12-9 shows the Query Designer for a select query from the authors table in the Pubs database. The top panel is the diagram pane. You right-click in this pane to add any table or previously built view as an input source for your new view. Select the check box next to a field to add it to the grid pane in the middle. Use the check boxes in the output column to indicate whether a field appears in the return set for a view. You can also add criteria in the columns to the right to restrict the rows that a view returns. If you prefer to write SQL rather than point and click, you can use the SQL pane at the bottom to write SQL statements.

Figure 12-9. The Query Designer.

The View Design toolbar and the menu bar offer additional help for writing views. The SQL button turns the SQL pane on and off. In the default arrangement, the button to the left of the SQL button performs the same function for the grid and diagram panes. The button to the right with the check mark-the Verify SQL button-performs a syntax check for SQL that you enter directly into the SQL pane. The Group By button next to the Verify SQL button modifies the grid pane to start the design of an aggregate query. Its behavior mimics the Totals (Σ) button in the classic Jet query builder. After you have the view the way you want it, click the New Object button to automate the preparation of a form or report based on the view. (If the view is not already saved, you will be prompted to save it first.)

NOTE

---

The Query Designer offers the benefits of graphical query design for building views without the overhead of attaching remote tables (as is the case with classic .mdb files). The SQL statements for the views execute on the database server, and the Access Project merely displays the return set.

You are not restricted to building views that pull data from the active connection for an Access Project. You can use the OPENROWSET keyword to point a view at a different database on the same server or even another database server. This can even be from a different type of database server, such as Oracle or Jet. The OPENROWSET keyword takes three arguments with comma delimiters. Enclose the first and second arguments in single quotes to designate them as strings. The third argument is a database object name. The first argument names the OLE DB provider for the alternate data source.

The two samples below use the OLE DB provider for ODBC-compliant data sources. This enables your custom view to connect to any ODBC-compliant data source. The second argument contains the connection string. The provider determines the correct syntax for this argument. The third argument is a database object name in SQL Server format. The general format for specifying the object is linkserver.catalog.schema.object. linkserver is the local SQL Server 7 name for the OLE DB provider that points at the remote, heterogeneous data source. If you are not performing a query for a non-SQL Server data source, you do not need this parameter. catalog is the database name, and schema is the database owner. object is the table name.

Figure 12-10 shows SQL pane representations of two views. Both are from the Project PubsFromCabxli.adp. For the test environment, this file resides on a computer named CABXLI, but its Data Link Properties dialog box indicates a connection with the Pubs database on a computer named cab2200. Each view uses the OPENROWSET keyword to connect to a data source other than Pubs on cab2200. The SelectFromAnotherDB view connects to the Shippers table in the NorthwindCS database on cab2200. The SelectFromAnotherServer view links to the Shippers table on the local server, CABXLI. The Query Designer automatically adds the DerivedTable1 term at the end of both SQL statements.

Figure 12-10. SQL syntax for the OPENROWSET keyword, which you use to connect to data sources outside the active connection.

Using Stored Procedures

You can use stored procedures to build on and extend the power of views in custom SQL Server applications. There is no Query Designer for stored procedures. Stored procedures compensate for this lack by using an enhanced vocabulary based on Transact-SQL, a special dialect of SQL for SQL Server. The flexibility of Transact-SQL along with the power of its stored procedures make this SQL dialect a candidate for your second or third programming language after VBA and ADO, especially if you plan to develop with Access Projects.

Figure 12-11 shows the result of a union query in an Access Project. Recall that a union query concatenates one recordset behind another. In this case, the code (shown below) concatenates the FamilyMembers table with the AdditionsToFamilyMembers table. The code and sample data for the union query are in adp1.adp on the companion CD. The AdditionstoFamilyMembers table references the adp1SQL database.

 SELECT FamID, Fname, Lname, Relation FROM AdditionsToFamilyMembers UNION SELECT FamID, Fname, Lname, Relation FROM FamilyMembers 

Figure 12-11. A union query in SQL Server (and MSDE) can concatenate two or more tables, but it cannot sort them.

This code is in a view named MyUnionQuery. Notice that the rows do not sort on FamID; there is no ORDER BY phrase in the SQL for the view. Recall that SQL Server views do not enable the ORDER BY phrase. If you want the records ordered by FamID or any other field, you must run a stored procedure.

The following sample adapts the SQL for the view and builds a stored procedure with more flexibility. In this case, a procedure named StoredProcedure1 does the concatenation and sorts by FamID.

 ALTER PROCEDURE StoredProcedure1 AS SELECT FamID, Fname, Lname, Relation FROM AdditionsToFamilyMembers UNION SELECT FamID, Fname, Lname, Relation FROM FamilyMembers -- Here's the ORDER BY phrase ORDER BY FamID 

The ALTER PROCEDURE phrase derives its name from its ability to change a procedure initially created with the CREATE PROCEDURE phrase. However, you can initially create a procedure with ALTER PROCEDURE. Notice that the syntax calls for the keyword AS after the ALTER PROCEDURE phrase. The balance of the stored procedure is exactly like the MyUnionQuery view, except for the last two lines. The next-to-last line shows the syntax for a comment that fits on one line. You start the line with two dashes. The final line contains the ORDER BY phrase that sorts the records by FamID.

There are many important reasons for becoming familiar with stored procedures when using Access Projects. The SQL Server and MSDE engines support a series of administrative functions through system stored procedures. These procedures reside in the master database. All these system procedures start with sp_. For this reason, you should avoid using sp_ in the names of your custom stored procedures.

A stored procedure named sp_server_info gives feedback about all kinds of server functionality. (SQL Server Books Online offers information about this procedure.) You can run it as a stored procedure in your application to determine property settings for a SQL Server engine that can help you decide how to program it. For example, the 18th row of a normal return set can indicate whether your server sorts in a case-sensitive or case-insensitive fashion. The following sample builds a simple stored procedure in your application that taps the system-stored procedure sp_server_info to determine the collation convention on the server that you are currently connected to. This sample also shows the format for a comment that extends over more than one line.

 Alter Procedure DetermineSortOrder /*If row 18 shows sort_order - nocase, then sorts are case insensitive. */ AS EXEC sp_server_info 18 

Another reason for learning about stored procedures is to use their parameters. You can pass parameters to them, and they can return values through output parameters. The DetermineSortOrder procedure pulls the 18th row from the return set generated by sp_server_info. The number 18 is a parameter that specifies which row the system procedure should display from its normal return set.

The two custom procedures below design stored procedures that accept parameters and programmatically pass them values. Custom stored procedures with parameters can automatically prompt for values if you do not pass them values programmatically. The first procedure performs two functions: It calls the procedure that generates a sales report, and then it passes values to the second procedure that designates start and end dates for the report as well as an employee name. You can edit the three parameters in the first procedure to vary the output of the second one.

 Alter PROCEDURE RunEmployeeSalesByDate AS EXEC "Employee Sales by Date" '1/1/95', '1/1/98', 'Buchanan' 

The second procedure above accepts its parameters as part of the ALTER PROCEDURE statement. Each parameter specification has two arguments: the name, such as @Beginning_Date, and the data type designation, such as datetime or varchar(20). The data type specification for an employee's name in the stored procedure must match the data type setting for Employee Name in the Employees table. The SQL statement joins three tables, and a WHERE clause puts the parameters to work.

 /*This procedure accepts three parameters to determine the  content for a sales report.     @Beginning_Date is the start date period.     @Ending_Date is the end period.     @lastname specifies the employee for which     the report lists sales. */ ALTER PROCEDURE [Employee Sales by Date]  @Beginning_Date datetime, @Ending_Date datetime, @lastname varchar(20) AS  SELECT Employees.Country, Employees.LastName, Employees.FirstName,      Orders.ShippedDate, Orders.OrderID,      "Order Subtotals".Subtotal AS SaleAmount  FROM Employees INNER JOIN (Orders INNER JOIN "Order Subtotals" ON      Orders.OrderID = "Order Subtotals".OrderID)      ON Employees.EmployeeID = Orders.EmployeeID  -- This format is for comments on a single line. WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date And     Employees.LastName = @lastname