SQL Server Functions

SQL Server comes with many built-in functions such as RTrim, GetDate, and many others. You can also create user-defined functions and call those functions as if they were built-in functions of SQL Server. User-defined functions can return a single value, such as the result of a calculation, or they can return an entire table of results.

You may be wondering how functions differ from stored procedures. User-defined functions have a lot in common with stored procedures because both are just SQL statements stored on the SQL Server. However, a user-defined function, unlike a stored procedure, can be embedded within a basic SQL statement, such as the following:

SELECT FormatDescription(ProductDesc) FROM tblProducts

In this instance, the function is called FormatDescription and will be called for each record selected in the SQL statement. If this code were contained in a stored procedure, a loop would have to be executed to call the stored procedure for each record.

When you open the designer from Access to add a Function, the Add Table dialog box appears, as shown in Figure 9.25. In this example, the function is based on the vwProductPrice view that you created in the prior example.

Figure 9.25

A designer similar to the other designers you have seen so far appears, as shown in Figure 9.26. In this example, the function will select the products that have a unit price greater than $500.

When you run the preceding function, all products that are greater than $500 are displayed, as shown in Figure 9.27.

At this point, you do not have to be an expert on determining when to use SQL Server stored procedures, view, or functions. It is a complicated topic that is beyond the scope of this introductory chapter. However, if you want to see more examples of how to use stored procedures, views, and functions in your applications, please consult the comprehensive case study in Chapter 13 that uses an Access Project with a SQL Server database.

Figure 9.26

fnExpensiveProducts

: Function

- n]Q

ProductName

Product De EC

UnitPrice \

XYZCo Marble Table

Marble patio table 54 inches rounc

S 1.850 00

*

Record: JjJ f

ikJLÖi^ fek.] of 1

Figure 9.27

Now that you have a basic familiarity of Access Projects, you can walk through the several steps involved in upsizing an existing Access database to an Access Project and SQL Server database.

0 0

Post a comment