Exago Logo
Search
Generic filters
Exact matches only

Table-Valued Functions

Table-valued functions (TVF) are user-defined functions that return a table data type as output. Unlike views, table-valued functions allow for more than one SELECT statement.

Below is the TVF we’ll be referencing in this guide. Note that the function RETURNS TABLE and takes the parameter startDate with the data type DATETIME.

ALTER FUNCTION "dbo"."OrderFunction" (@[email protected] DATETIME)
RETURNS TABLE
AS
RETURN (SELECT o.OrderId, o.CustomerId, o.EmployeeId, o.OrderDate
		FROM Orders o
		WHERE o.OrderDate >= @[email protected])

Adding Table-Valued Function Data Objects

Before adding the TVF as a Data Object, prepare the configuration by creating all parameters necessary for the function. In this example, the @[email protected] parameter. Make sure the parameter data type matches that specified in the function.

Caution

Selecting the TVF containing this parameter as the Parameter Dropdown Object will cause an error.

With the parameter applied, continue by locating the TVF in the admin console as you would a standard table. Use either the Automatic Database Discovery tool or select the function from the New Data Object name dropdown menu.

After selecting the TVF, give it an Alias, then add the Parameters.

Complete the rest of the New Data Object form as if it were standard database table. Click Apply to save the Data Object.

It is possible to join a TVF object to other Data Objects. See Joins for more information.

Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents