لطفا منتظر باشید ...
SQL Server Objects
Now that we have upsized the database, we will look at the major objects your queries will be changed to.
Views
Think of a view as a window into your data. Views allow you to create 'tables' on the fly, usually to permit users to have access to a subset of data. Views can have many advantages:
Hide complex SQL from users
Used to limit direct access to tables
Reduce data returned from the server
Group date from different table together
For example, consider the following T-SQL statement:
SELECT
dbo.tblCompany.CompanyName, dbo.tblCompany.Address,
dbo.tblCompany.City, dbo.tblCompany.State, dbo.tblCompany.ZipCode,
dbo.tblSales.fkIceCreamID, dbo.tblSales.Quantity, dbo.tblSales.
DateOrdered, dbo.tblSales.DateDispatched, dbo.tblSales.DatePaid
FROM
dbo.tblCompany INNER JOIN
dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.fkCompanyID
Look at the next bit of SQL:
SELECT
dbo.vw_sales.*
FROM
dbo.vw_sales
This returns the same records as the above SQL, but this time we are using the view that will be created in the next example. Note that we don't have to worry about the complex SQL this time. A simple
SELECT statement is all that is required.
Note the use of the prefix
dbo before each table name.
dbo is the table owner. It is possible for different users to create objects with the same name. For example, user Martin could create a table called
Customer; user Patricia could also create a table of the same name. When referencing the tables, you would prefix the table name with the table owner name, for example,
Martin.Customer . When creating Stored Procedures in an Access 2002 ADP, your table will have the prefix of
dbo; in this case, this is the system administrator. It is good practice to have all tables owned by
dbo rather than have multiple table owners.
Try It Out-Creating a View
Microsoft Access 2002 uses the "Query Builder" to create views in ADPs. This is a very handy tool when working with SQL Server and a big advance over Access 2000. To create a view in the ADP:
Select
Queries | New.
In the dialog, select
Design View.
Click
OK to open the Query Window.
Creating the view is a simple matter of selecting the tables and indicating the fields required. Select
tblcompany and
tblSales and close the dialog.
Select the fields you would like from both tables (click the checkbox beside the field names).
Your window should look as follows. In order to view the SQL statement being generated select
View | Show Panes | SQL Pane from the main menu, or click the
SQL button on the toolbar.
You must now save the view.
Execute the code by clicking
Run in the main menu.
Save the view as
vw_SalesExample in response to the prompt. You must save the view before you can execute it.
When you come to use the view, you simply treat it as a table in the normal way. However, there is one important difference; usually this view cannot be updated as it uses multiple tables, so you need to be careful when using a view as the record source for a form. However, a view that is based on a single table is updateable once you change some of its properties.
Try It Out-Updating Data via a View
For this example, create a view using Microsoft Access which selects all the fields from
tblCompany . To select all fields from a table, right-click the mouse button and select
All Columns from the context menu. Simply follow the instructions from the earlier example, only this time select a single table. Save the view as
vw_Customer . Once you have created the view, run the form wizard to create a simple form based on
vw_Customer . On this occasion, the data can be updated via the view as the view is based on a single table.
In order to permit a normal user to edit data via a view-based form, it is important that you change one of the view properties. When working with views, the usual setup is to grant permission to the users on the view only, without giving permission to the underlying table. That being the case, the user has no permissions to work directly with the table data. SQL Server will therefore not permit any updates to the table. When you try to update the data via the form, SQL Server will attempt to update the table directly. If your user has no permissions on the table, the update fails. However, by changing one of the view properties, we can force Access to update the table via the view instead.
To see the property sheet for the view click
Queries , then select the
View in the Database window.
Open the view in
Design View.
Select
View |
Properties using the main menu.
Check the box beside
Update using View Rules to enable this option.
Close the dialog and save the changes.
The view can now be used as the record source of a form to display company data. Any user with access to the view, but not the base table, will now be able to use the customer form based on
vw_company , even though they do not have permission on the actual customer table on SQL Server.
Again, views provide you with access to data without giving users permissions to actually access tables directly. We know several developers who base all ADP applications on views rather than tables. One additional benefit to this is that the user does not even see the table in the database window. As far as they are concerned the table doesn't exist. So are there any problems with views? Of course there are:
Views do not allow
ORDER
BY because a view is a virtual table, and tables, like views, cannot be ordered.
However, the use of the SQL
TOP command can circumvent this restriction. For example, the following is the syntax to add an
ORDER
BY to one of the views created earlier:
SELECT
TOP 100 PERCENT dbo.tblCompany.CompanyID, dbo.tblCompany.CompanyName,
dbo.tblCompany.Address, dbo.tblCompany.City,
dbo.tblSales.Quantity, dbo.tblSales.DateOrdered,
dbo.tblSales.DateDispatched
FROM
dbo.tblCompany INNER JOIN
dbo.tblSales ON dbo.tblCompany.CompanyID
= dbo.tblSales.fkCompanyID
ORDER BY dbo.tblCompany.CompanyID
Views do not permit parameters.
If you try to add a parameter to a view within the graphical designer, then SQL Server responds with an error. Try it out in the Access Interface. As we shall see, User-Defined Functions offer you similar functionality, which replaces the need for parameter views.
User-Defined Functions (UDFs)
SQL Server supports two types of UDFs - Scalar Functions and Inline Table Valued Functions. As usual with Access, there are a number of ways to design the function, but for Inline Functions, you can use the graphical Query Builder. SQL Server also provides you with hundreds of built-in functions. Below is a brief sample of the more useful functions:
Access | SQL Server |
|---|---|
Day() | DatePart(dd,date) |
Month() | DatePart(m,date) |
Date() | Convert(varchar, getdate(),101) |
Now() | GetDate() |
Month | DatePart(mm,date) |
UCase | Upper() |
LCase | LCase() |
Ltrim | (Ltrim) |
In addition to standard functions, SQL Server also provides the following System Functions:
Function | Comment |
|---|---|
@@IDENTITY | Returns the Primary Key of the last record inserted |
@@ERROR | Returns the error number of the last statement called |
@@ROWCOUNT | Returns the number of records affected by an SQL statement |
@@ServerName | Returns the name of the server |
| Important | While @@IDENTITY returns the value from the last insert, it really means THE LAST insert. If two users insert a record, the result of running @@IDENTITY will be the latest insert, which may not be the one you require. SQL Server 2000 provides a new function to return the identity value of the last insert: Scope Identity . This returns the identity value from the context in which it is called. |
SQL Server Books online provides copious information on the hundreds of functions available in SQL Server 2000.
Functions are classified as either deterministic or non deterministic. A deterministic function will always return the same value when called. A non-deterministic function on the other hand returns a different value when called. For example,
GetDate() will always return a different value when you call it, as it will always include the date and time portion of a date which is always different. This distinction is important when creating your own functions as there are restrictions placed on function use. For example:
Scalar FunctionsScalar functions return a single data value (not a table) with a
RETURNS clause. Scalar functions can use all scalar data types, with exception of
timestamp and user-defined data types
Inline FunctionsInline functions are much like a SQL Server view, only on this occasion you can pass parameters to the function. Again, Access permits you to create the function using the graphical interface. An Inline Function returns a data type in the form of a table and you can then reference the function in the
SELECT statement of a SQL statement.
Try It Out-Creating an Inline Function
From the Database window:
Select
Queries |
New.
Select
Design In-Line Function from the dialog.
Add the
Company and
Sales tables, using the
Add Table dialog. At this point (once you select the fields), your function should look like the following:
Close and save the function as
udf_Customerspaid. The
u prefix indicates that this is a user created function.
The function is executed just as you would a standard Access query. Simply double-click on it in the Database window, or select the function and click
Run on the Access tool bar.
The function can be used in place of a table. For example, the following stored procedure uses the function output as if it were a standard table:
CREATE PROCEDURE dbo.usp_CustPaid
(@ID int)
AS
SELECT
CompanyID, CompanyName, Address, City, Quantity, DateOrdered
FROM
dbo.udf_Customerspaid() udf_Customerspaid
WHERE
(CompanyID = @ID)
In addition, the function can also be used in the
JOIN clause of an SQL Statement.
| Important | If you're still learning SQL, the easy way to create the SQL statement used by the function is to build a Stored Procedure graphically and the cut and paste the SQL from the procedure into the function. This way you know the syntax is correct. In fact any time you're working with VBA and SQL strings this is a good way to check the syntax of SQL if you receive an error message. In fact why bother typing it in the first place? Cut and paste it in from the Query Builder. |
Multi-Line User-Defined Functions
In this case you get no help from the Query Builder, and must enter the code by hand. Multi-Line UDFs allow you to build up very sophisticated functions, which, in this case, return a table data type that you can define yourself. Our next example returns the sales from two customers whose IDs are passed to the function. Shortly we shall see this function type applied to one of the queries that failed to upsize in our
IceCream database.
Another form you will have to amend is
frmSalesFigures - those data sources are set to the old Access query. Simply change the form and the combo box record sources to
usp_qrysalessummary to fix the form and all will be well. What? It didn't work! Let's see why…
Try It Out-Fixing the Combo using a UDF
The combo box fails to work because of the VBA code used in the
OnChange event. We will not show the entire code here, just the relevant lines:
If cboCompany = -1 Then
strSQL = "qryxSalesSummary"
Else
strSQL = "SELECT * FROM qryxSalesSummary WHERE CompanyName="" & _
cboCompany.Column(1) & ""
End If
Note that again we are using the old Access query as the source for
strSQL in the initial
If statement, otherwise we are running a
SELECT statement filtered by column
1 of the combo box. Later we will use a stored procedure to replace the sales summary query. On this occasion we are going to write a UDF that returns a table to carry out the same function. The following is the text of the UDF:
CREATE Function dbo.SalesSummary
()
Returns @salessummary TABLE
(
CompanyName NvarChar(50),
MonthName nvarchar (20),
Quantity Int,
MonthNumber Int
) as
Begin
INSERT @salessummary
SELECT
dbo.tblCompany.CompanyName,
DATENAME(MONTH, dbo.tblSales.DateOrdered) AS MonthName,
SUM(dbo.tblSales.Quantity) AS Quantity,
MONTH(dbo.tblSales.DateOrdered) AS MonthNumber
FROM
dbo.tblCompany INNER JOIN
dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.
fkCompanyID
GROUP BY dbo.tblCompany.CompanyName, DATENAME(MONTH,
dbo.tblSales.DateOrdered), MONTH(dbo.tblSales.DateOrdered)
ORDER BY dbo.tblCompany.CompanyName, DATEPART(m, dbo.tblSales.DateOrdered)
RETURN
END
To create the function in the Access interface:
From the database window, click
Queries |
New
Select
Create Text Table Valued Function in the dialog
The function template opens with some boilerplate structure already in place:
The easiest way to proceed is to delete the template text and enter the function shown above.
Save and close the function accepting the default name which you have already defined in the
CREATE statement.
Double-click the function name in the Database window to execute it.
The date functions used are discussed following the next example Stored Procedures.
Stored Procedures
Stored Procedures form the backbone of many SQL Server applications and are widely used to provide data access to the tables. Using stored procedures, we can grant access to the procedure while protecting our table structure. All interaction between the user and the data is via the stored procedures the user is given permission to use. Another advance in Access is the ability to build simple stored procedures via the user interface. However, for more complex procedures, those that contain additional logic, you are still required to write them by hand.
What is a Stored Procedure?
A stored QueryDef object. However, SQL Server stored procedures differ in many ways, not least the fact that using T-SQL we can build conditional logic into the SQL, using
CASE ,
IF…THEN…ELSE and other programming structures. The first time a stored procedure is executed, SQL Server creates an execution plan, which is the optimal way to actually retrieve your data or perform the function of the procedure. This plan remains in memory and is reused by the procedure on each execution.
The Query Builder
The actual window will have changed slightly, but the basic operation remains the same. The window is still divided into two panes, the top pane is used to display the tables, while the bottom pane is used to display the fields of output and any criteria or sorting you wish to apply. You may also add a SQL pane by selecting
View | Show Panes | SQL using the main menu or by clicking the
SQL button on the toolbar.
You Properties to open the
Properties dialog for this stored procedure.
There are three tabs available to you on the dialog:
Stored Procedure
Stored Procedure Parameters
Stored Procedure Tab
Using this page, we can set some properties that affect the stored procedure and the way in which it returns data:
Output all Columns
Selecting this option will return all the columns from all the tables available in the query windows.
DISTINCT Values
Removes duplicate values form the output.
Set NOCOUNT on (Remember to set it back to
OFF when you are done with the query).
Indicates whether SQL Server returns a count of the records returned by the stored procedure to the client. Setting
NOCOUNT to
ON , the count is not returned. For stored procedures which do not return data, this can lead to large performance improvements when
NOCOUNT is set to
ON .
TOP is used to select a group of records, for example, the top 10 customers. If we also click the
PERCENT box, we would then return the top 10% of customers.
Description and
Comment
This is a free text field to allow you to add notes to the procedure.
Stored Procedure Parameters Tab
This tab is only available once you have added a parameter to the procedure. However, once you have added a parameter, it is added to this tab.
You can also provide a
Default parameter, which will be used if there is no input to the procedure by the application or the user.
Data Tab
There are several properties available under the
Data tab:
Filter
When data is returned to the client, the filter will be applied. This is a
WHERE clause without the
WHERE keyword.
Order By
A field or fields used to order the result set on the client.
Subdatasheet Name
The help file recommends setting this file to
Auto ; however, many users have seen performance fall as a result when using this setting in Access 2000 and have agreed within the Microsoft Newsgroups that this setting should be place at
None .
Link Child Fields
Shows the fields used to link the subdatasheet selected in the property above.
Link Master Fields
Related field in the subdatasheet.
Subdatasheet Height
Height of the subdatasheet.
Subdatasheet Expanded
Expands the subdatasheet when viewed in data view.
Default View
The default view for the subdatasheet.
As you can see there are several stored procedure properties that you can set within Query Builder itself. Let's move on and create a procedure in our ADP
Try It Out-Creating a Stored Procedure
AccessChapter 21 on ADO. In this section, we will look at working with the Graphical Builder in a bit more detail.
From the Database window, select
Queries |
New.
Select
Design Stored Procedure from the dialog.
This opens the graphical stored procedure tools. This is one of the major advances Access 2002 has made. The ability to create stored procedures is similar to that we used to create Access Queries. The window you use is very similar to the Query Builder in Access. You still have the
Add Table Dialog, only on this occasion you will find two new items,
Views and
Functions . You can also use these server objects when building a stored procedure.
Select
tblIceCream and
tblCreamIngredient and
tblIngredient.
Select any fields in addition to
tblIngredient.IngredientID as the additional fields are not important to this example.
In the
Sort Type , select
tblIceCream.IceCream as the sorting field.
In the criteria cell for the
Price value enter
= @IngID.
Close and save the procedure as
usp_ingredients.
Once the stored procedure has been saved, it can then be used as the record source for forms and reports. However, unlike an Access Query, it cannot be used as the basis for another stored procedure. However, it is possible to call one stored procedure from within another. If you do need this functionality, then create a view or a UDF instead.
| Important | Remember before your users can execute any object on SQL Server they need to have the required permissions granted. The easy way to do this is by using Enterprise Manager, the graphical administration tool that comes with the server. However, those using SQL Desktop Engine will find that they have no user interface with which to manage the instance of the server unless they have downloaded the evaluation edition of SQL Server 2000, which does include a non-time-expired copy of the Enterprise Manager. However, its continued use does break Microsoft SQL Server license requirements. We will be looking at programming security at the end of this chapter. |
| Important | A word on parameters: Parameters in stored procedures are indicated using the at symbol (@). They can be of several types - input parameters as above, or output parameters, which can be passed from the procedure.When you are using a parameter to pass a value into a stored procedure, do not use the default property in the procedure property sheet. The default is supposed to be used if the user or application does not pass a parameter to the procedure; however, it does not work when used in the Access interface. Using the default value results in the procedure no longer requesting a parameter as it goes directly to the default. |
Finally we have got here. Execute the query using a value of
31 for the parameter. One record should be returned.
