Taking Advantage of In-line Functions

SQL Server 2000's in-line functions let you emulate Jet's parameterized queries. Views don't accept parameters, but you can choose between table-returning functions and stored procedures. Functions and stored procedures accept one or more input parameters, which can emulate Jet parameters. One of the advantages of a parameterized function is that it can substitute for a table name in the FROM clause of a SELECT query.

To review Jet parameterized queries, see "Designing Parameter Queries," p. 440.

Creating a Parameterized Table-Returning Function

NorthwindCS doesn't include a function, so do the following to create a new parameterized in-line function:

1. 

   In the Database window's Queries page, click New to open the New Query dialog (see Figure 20.20). The choices in the list differ considerable from those of Jet and Access 2000's version.

   Figure 20.20. The New Query dialog for ADP gives you the choice between using the project designer (Design...) or typing T-SQL statements in a text editor window (Text...).

   

2. Double-click the Design In-Line Function item to open Function1 in Design view with the Add Table dialog active.

3. Select the Orders table, click Add, and close the Add Table dialog.

4. Mark the OrderID, CustomerID, OrderDate, RequiredDate, and ShippedDate check boxes of the field list to add these columns to the grid. Add an ascending sort on the OrderDate column.

5. To add date-based input parameters to the OrderDate field, type the Jet version of a pair of date parameters BETWEEN [Start Date] AND [End Date] in its Criteria cell (see Figure 20.21). In this case, the square brackets specify an input parameter, not that the parameter names include a space. When you tab past the cell, the statement changes to T-SQL parameter syntax BETWEEN @Start_Date AND @End_Date. T-SQL doesn't permit spaces in parameter names.

   Figure 20.21. Specifying function parameters is similar to adding Jet query parameters.

   

6. Click the Run button, click Yes when asked whether you want to save your query, type a name for the function such as fnOrdersByDate in the Save As dialog, and click OK.

7. A slightly modified version of the Jet Enter Parameter Value dialog opens for the Start_Date parameter. Type 1/1/1997 and click OK to open the End_Date dialog. Type 12/31/1997 and click OK to view the result set of the function in Datasheet view (see Figure 20.22).

   Figure 20.22. Datasheet view of the function confirms the Start_Date parameter works. Scroll to the bottom of the datasheet to check for the End_Date parameter.

   

Adding Default Values for the Input Parameters

A feature added to the Access 2002 project designer is support for parameter default values. Jet's Enter Parameter Value dialog doesn't support default values.

To add default values for the Start_Date and End_Date parameters, do this:

1. Return to Design view, click the properties button or right-click the window and choose Properties to open the function's Properties dialog.

2. Click the Function Parameters tab to display a list with the two parameter names and their data type (datetime).

3. Type the default values for the two parameters in the default column. As you move to the second parameter, the first default value is enclosed by single quotes (see Figure 20.23).

   Figure 20.23. The Function Parameters page is blank, unless you add input parameters by the procedure in the preceding section. Add default values for the parameters in the Default column.

   

   Note

   SQL Server accepts only character values for dates. The default delimiter for T-SQL character values is the single quote or apostrophe ('). Jet's # delimiter doesn't apply to TSQL date values.

4. Click the Run button, save your changes, and open the drop-down list of the Start_Date Enter Parameter Value dialog and select <DEFAULT> (see Figure 20.24). The other option is <NULL>, which passes a NULL value to the parameter. Stored procedures and functions often include T-SQL code to act on NULL parameter values. Click OK to display the End_Date Enter Parameter Value dialog.

   Figure 20.24. Enter Parameter Value dialogs for functions and stored procedures have a drop-down list from which you can select a previously specified default or a NULL value.

   

5. Select <DEFAULT>, and then click OK in the End_Date Enter Parameter Value dialog to open the function's datasheet.