Work with user-defined functions

Note   The hyperlink in this topic goes to the Web. You can switch back to Help at any time.

Microsoft Access documents in its Help system and in The Microsoft Office XP Developer's Guide how to use and program Microsoft Access project that is connected to a Microsoft SQL Server database. However, you may also need additional information about SQL Server to answer specific product questions you might have, such as reference information on Transact-SQL or the SQL Server Enterprise Manager.

Access to SQL Server Books Online and Transact SQL Reference information

The primary source of information about Microsoft SQL Server is SQL Server Books Online. If you have installed Microsoft Office XP Developer or you have access to SQL Server 2000 on either a local or remote computer, then you have access to SQL Server Books Online through the Start menu. However, if you don't have access to SQL Server Books Online, you can download it from the SQL Server Books Online download Web page.

An overview of the SQL Server documentation

SQL Server documentation contains the following information.

When your Microsoft Access project is connected to a Microsoft SQL Server 2000 database, you can create and use user-defined functions. The following example shows the ListCust user-defined function that accepts a named parameter, returns a table, and is used in the FROM clause of a SELECT statement.

SELECT * FROM ListCust(@[Enter a Name])

Overview of user-defined functions

A user-defined function is a query, like a view or stored procedure, that you can:

• View in the database window.

• Create in the Query Designer or SQL Text Editor.

• Use to retrieve data and browse the results in a datasheet

• Use as the record source of a form, report, or combo box.

  Note   You cannot use a user-defined function as the record source of a data access page.

• Programmatically manipulate using the Access object model.

User-defined functions combine the best features of views and stored procedures into a single query that you can nest, pass parameters to, sort, and return values. In many cases, user-defined functions are a better alternative to views and stored procedures because you can return a single table of data or scalar value, hide the business logic and details of how that data or scalar value was created, and simplify the complexity of your SQL statement syntax.

For more information about and examples of user-defined functions, see the SQL Server documentation.

The three types of user-defined functions

Based on what kind of value it returns, each user-defined function falls into one of three categories:

in-line user-defined function   Contains a single SELECT statement and returns an updateable table of data. You can use this function in the FROM clause of a query. You can graphically create and edit an inline user-defined function by using the Query Designer. You can also define extended properties.

table user-defined function   Contains one or more SELECT statements and returns a non-updateable table of data. You can use this function in the FROM clause of a query. You can create and edit a table user-defined  function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a table user-defined function by using the Query Builder. However, you cannot define extended properties.

scalar user-defined function   Contains one or more SELECT statements and returns a scalar value, such as the int, decimal, varchar, sql_variant or table data types. You can use a scalar function in a query anywhere you would use a column name. You can create and edit a scalar user-defined  function by using the SQL Text Editor, and you can even graphically edit an SQL SELECT statement in a scalar user-defined function by using the Query Builder. However, you cannot define extended properties.

Create an in-line user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Design In-Line Function.
3. Design the in-line user-defined function in the Diagram and Grid panes of the Query Designer.
4. Click Save on the toolbar.
5. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the  in-line user-defined function to Queries under Objects in the Database window.

Create a table user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Create Text Table-Valued Function.

   Access displays the SQL Text Editor with the following skeleton SQL syntax:

   CREATE FUNCTION "Function1"
       (
       /*
       @parameter1 datatype = default value,
       @parameter2 datatype
       */
       )
   RETURNS /* @table_variable TABLE (column1 datatype, column2 datatype) */
   AS
       BEGIN
           /* INSERT INTO @table_variable
               sql select statement */
           /* alternative sql statement or statements */
       RETURN
       END

   Write the table user-defined function by using Transact-SQL. For more information on Transact-SQL, see the SQL Server documentation.

3. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the table user-defined function to Queries under Objects in the Database window.

Note   You can graphically edit an SQL SELECT statement in a table user-defined function by using the Query Builder.

Create a scalar user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Create Text Scalar Function.

   Access displays the SQL text editor with the following skeleton SQL syntax.

   CREATE FUNCTION "Function1"
       (
       /*
       @parameter1 datatype = default value,
       @parameter2 datatype
       */
       )
   RETURNS /* datatype */
   AS
       BEGIN
           /* sql statement ... */
       RETURN /* value */
       END

   Write the scalar user-defined function by using Transact-SQL. For more information on Transact-SQL, see the SQL Server documentation.

3. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the scalar user-defined function to Queries under Objects in the Database window.

Note   You can graphically edit an SQL SELECT statement in a scalar user-defined function by using the Query Builder.

Note   You cannot use a user-defined function as the record source of a data access page.