Chapter 4: Functions

Microsoft has done a fantastic job providing database administrators and developers with an extensive set of built-in functions for SQL Server. Since SQL Server 2000, you are also able to create your own T-SQL functions. In SQL Server 2005 it is possible to design functions in .NET languages as well. We will cover the design of T-SQL user-defined functions in detail in Chapter 10 and .NET functions in Chapters 11 to 14, and focus here on the uses of functions and their attributes.

Using Functions

Functions are T-SQL elements that are used to evaluate zero or more input parameters and return data to the caller. The syntax for calling a function is

 Function_name   ([parameter]     [,...n]) 

For example, the Sin() function has the following syntax:

      Sin(float_expression) 

So, to display the sine of 45 degrees, you would use

 SELECT   Sin(45) 

Some functions accept more than one parameter, and some do not require parameters at all. For example, the GetDate() function, which returns the current date and time on the system clock to the caller, accepts no parameters. We will use the GetDate() function to illustrate the most common ways to use functions in T-SQL.

In Selection and Assignment

Functions can be used to represent a value or a part of a value to be assigned or selected in a Set or Select statement. In the following example, two variables are populated using values stored in the selected record and a third variable is populated using a function:

 Select  @chvMake = Make,         @Model = Model,         @dtsCurrentDate = GetDate() from dbo.Equipment where EquipmentID = @intEqId 

As previously noted, this use is not limited to the Select statement. Values can be assigned in the Set statement, displayed in the Print statement, stored in a table using Update and Insert, or even used as parameters for other functions:

 Create Procedure dbo.ap_Schedule_Insert      @intLeaseId int,      @intLeaseFrequencyId int As Insert dbo.LeaseSchedule(LeaseId, StartDate,                          EndDate, LeaseFrequencyId) Values (@intLeaseId, GetDate(),         DateAdd(Year, 3, GetDate()), @intLeaseFrequencyId) Return 

This procedure inserts the current date using the GetDate() function in the StartDate column. The EndDate column is calculated using the DateAdd() function, which accepts the GetDate() function as one parameter. It is used to set the end date three years from the current date. This was just an example of the usage of functions; you will be able to see more details about GetDate() and DateAdd() in the "Date and Time Functions" section of this chapter.

As Part of the Selection Criteria

Functions are often used in the Where clause of T-SQL statements:

 SELECT Inventory.InventoryId FROM LeaseSchedule INNER JOIN Inventory      ON LeaseSchedule.ScheduleId = Inventory.LeaseScheduleId WHERE (LeaseSchedule.EndDate < GetDate()) AND (Inventory.Rent <> 0) 

This Select statement selects the lease schedules that have reached the end of the term by comparing EndDate to the current date.

In Expressions

You can also use functions anywhere you can use an expression, such as in an If statement, which requires a Boolean expression to determine further execution steps:

 If @dtmLeaseEndDate < GetDate()     Begin         ...      end 

As Check and Default Constraints

Functions can also be used to define Check and Default constraints:

 ALTER TABLE [dbo] . [Order]  (      [OrderId]  [int] IDENTITY (1, 1} NOT null ,      [OrderDate]  [smalldatetime] NOT null ,      [RequestedById]  [int] NOT null ,      [TargetDate]  [smalldatetime] NOT null ,      [CompletionDate]  [smalldatetime] null ,      [DestinationLocationId]  [int] null ) ON [PRIMARY] GO ALTER TABLE [dbo].[Order] WITH NOCHECK ADD    CONSTRAINT [DF_Order_OrderDate] DEFAULT (GetDate()) FOR [OrderDate],      CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED (    [OrderId] )   ON [PRIMARY] GO 

In this case, the Order table will automatically set the OrderDate field to the current date if a value is not supplied.

Instead of Tables

Because SQL Server has a table data type, it is also possible for a function to return a recordset. Such functions are referred to as table-valued junctions. These functions can be used in T-SQL statements anywhere tables are expected. In the following example, the result of the function is joined with a table (dbo.EqType) to produce a new result set:

 declare @dtmLastMonth datetime set @dtmLastMonth = DateAdd(month, -1, GetDate()) Select * from dbo.fnNewEquipment ((@dtmLastMonth) NewEq inner join dbo.EqType  EqType on NewEq.EqTypeId = EqType.EqTypeId 

To reference any user-defined function including a table-valued function, you must specify the object owner along with the function name (owner.function).

The only exception to this rule is in the use of built-in table-valued functions. In this case, you must place two colons (::) in front of the function name. For example, the fn_ListExtendedProperty() function lists properties of the database object (see Figure 4-1). For more details about extended properties, see Chapter 15.

Figure 4-1: Using table-valued user-defined functions