Table-valued User-defined Functions

Since SQL Server has a table data type, it is possible to design a user-defined function that returns a table. The primary use of table-valued user-defined functions is similar to the use of views. However, these functions are far more flexible and provide additional functionality.

You can use a table-valued user-defined function anywhere you can use a table (or view). In this respect, table-valued user-defined functions implement the functionality of views, but functions can have parameters, and therefore are dynamic. Views are also limited to a single Select statement. Functions can have one or more Transact-SQL statements inside, enabling them to implement more complex functionality. That is why functions of this type are often referred to as multistatement table-valued user-defined junctions. Stored procedures can also return a result set, but the use of such result sets is somewhat limited. For example, only a result set returned by a function (and not a stored procedure) can be referenced in the From clause of a Select statement.

To demonstrate this functionality, the following Select statement references the user-defined function fnDueDays(), which returns a list of lease payment due dates. The statement returns a list of remaining payments and due dates.

 select DD.Termld, DD.DueDate, Inventory.Lease from dbo.fnDueDays('12/1/2005','12/1/2009','monthly'} DD, Inventory where InventoryId = 8 and DD.DueDate > GetDate() 

The result looks like this:

 TermId       DueDate                 Lease -----------  ----------------------- ---------- 1            2006-01-01 00:00:00     87.75 2            2006-02-01 00:00:00     87.75 3            2006-03-01 00:00:00     87.75 4            2006-04-01 00:00:00     87.75 5            2006-05-01 00:00:00     87.75 6            2006-06-01 00:00:00     87.75 7            2006-07-01 00:00:00     87.75 ... 

The stored procedure ap_Terms_List has functionality similar to the functionality of the fnDueDates() function. But to perform additional filtering of the result set returned by the stored procedure, you would first need to load the result set into a temporary table:

 Create Table #tbl(TermId int, DueDate smalldatetime) Insert Into #Tbl(TermId, DueDate)      Exec dbo.ap_Terms_List '12/1/2005','12/1/2009','monthly' Select #tbl.Termld, #tbl.DueDate, Inventory.Lease From #tbl, Inventory Where InventoryId = 8 And #tbl.DueDate > GetDate() Drop Table #tbl 

This is much more complicated than using the comparable function. Let's investigate the internals of the fnDueDates() function:

 Create Function dbo.fnDueDays -- return list of due days for the leasing (     @dtsStartDate smalldatetime,     @dtsEndDate smalldatetime,     @chvLeaseFrequency varchar(20) ) Returns @tblTerms table     (     TermID int,     DueDate smalldatetime     ) As Begin Declare @insTermsCount smallint -- number of intervals Declare @insTerms smallint -- number of intervals -- calculate number of terms Select OinsTermsCount =   Case @chvLeaseFrequency      When 'monthly'                 then DateDIFF(month, @dtsStartDate, @dtsEndDate)      When 'semi-monthly'                 then 2 * DateDIFF(month, @dtsStartDate, @dtsEndDate)      When 'bi-weekly'                 then DateDIFF(week, @dtsStartDate, @dtsEndDate)/2      When 'weekly'                 then DateDIFF(week, @dtsStartDate, @dtsEndDate)      When 'quarterly'                 then DateDIFF(qq, @dtsStartDate, @dtsEndDate)      When 'yearly'                 then DateDIFF(y, @dtsStartDate, @dtsEndDate)   End -- generate list of due dates Set @insTerms = 1 While @insTerms <= @insTermsCount Begin   Insert @tblTerms (TermID, DueDate)   Values (@insTerms, Convert(smalldatetime, CASE         When @chvLeaseFrequency = 'monthly'              then DateADD(month,@insTerms, @dtsStartDate)         When @chvLeaseFrequency = 'semi-monthly'         and @insTerms/2 = Cast(@insTerms as float)/2              then DateADD(month, @insTerms/2, @dtsStartDate)         When @chvLeaseFrequency = 'semi-monthly'         and @insTerms/2 <> Cast(@insTerms as float)/2              then DateADD(dd, 15,                           DateADD(month, @insTerms/2, @dtsStartDate)         When @chvLeaseFrequency = 'bi-weekly'              then DateADD(week, @insTerms*2, @dtsStartDate)         When @chvLeaseFrequency = 'weekly'              then DateADD(week, @insTerms, @dtsStartDate)         When @chvLeaseFrequency = 'quarterly'              then DateADD(qq, @insTerms, @dtsStartDate)         When @chvLeaseFrequency = 'yearly'              then DateADD(y, @insTerms, @dtsStartDate)         End , 105))     Select @insTerms = @insTerms + 1 End Return End 

Let me point out to you a few differences between these functions and scalar functions. User-defined functions that return a table have a table variable definition in the Returns clause:

 ... Returns @tblTerms table     (     TermID int,     DueDate smalldatetime     ) ... 

In the body of the function, there are statements that fill the contents of the table variable:

      Insert @tblTerms (TermID, DueDate)      Values (@insTerms, Convert(smalldatetime, CASE                          When @chvLeaseFrequency = 'monthly' 

The Return statement at the end of the function does not specify a value. As soon as it is reached, SQL Server returns the contents of the table variable to the caller:

 Return End 

Inline Table-valued User-defined Functions

An inline table-valued user-defined function is a special type of table-valued user-defined function. Its purpose is to implement parameterized views.

The syntax of an inline table-valued user-defined function is a bit different from the syntax of other functions:

 Create Function [schema_name.]function_name (      [ {@parameter_name scalar_data_type [= default]} [,...n] ] ) Returns Table         [With {Encryption Schemabinding}[,...n] ] [As] | Return (select-stmt) 

You do not have to define the format of the return value. It is enough to specify just the Table keyword. An inline table-valued function does not have the body of a function. A result set is created by a single Select statement in the Returns clause. It is best to demonstrate this feature with an example. The following function returns only a segment of a table based on a role the user belongs to. The idea is that a manager or any other employee can see only equipment from his own department:

 Create Function dbo.fn_DepartmentEquipment      ( @chvUserName sysname ) Returns table As Return (       Select Inventoryld, Make + ' ' + model Model, Location       From dbo.Inventory Inventory inner join dbo.Contact C       On Inventory.OwnerId = C.Contactld             Inner Join dbo.Contact Manager             On C.OrgUnitId = Manager.OrgUnitld                   Inner Join dbo.Equipment Equipment                   On Inventory.EqId = Equipment.Eqld                         Inner Join dbo.Location Location                         On Inventory.LocationId = Location.Locationld       Where Manager.UserName = @chvUserName      ) Go 

You can use this function in any place where a view or table is allowed, such as in a Select statement:

 Select * From fn_DepartmentEquipment ('deJans') Go 

The result of such a statement will be

 InventoryId Model                               Location ----------- ----------------------------------- -------------------- 32          3COM Palm Pilot Pro                 First Canadian Place 32          3COM Palm Pilot Pro                 First Canadian Place 8           Bang & Olafson V4000            Trigon Tower 8           Bang & Olafson V4000            Trigon Tower 31          Brother PL-1700                     First Canadian Place 31          Brother PL-1700                     First Canadian Place ...