Scroll

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.

 

Adding Table-Valued Function Data Objects

Before adding the TVF as a data object, prepare your configuration by creating any and all parameters necessary for the function. In this case, we need to create the startDate parameter. Make sure the parameter data type matches that specified in the function. 

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

 

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

 

After selecting the TVF you wish to add as an object, give it an Alias and then add the requisite Parameters.

Note. Add the Alias and Parameters before filling out the rest of the New Data Object fields to avoid error messages.

Complete the rest of the New Data Object form as you would for a standard table. Click 'Apply' to save the object.

Joining a Table-Valued Function

It is possible to join a TVF object to one or more other objects. Begin by adding a new join.

 

Then, in the New Join form, open the 'From Object - To Object' dropdown menu. Select the TVF from the left menu and the object to which you're joining it from the right menu.

 

Continue filling out the New Join form as you would for a standard table. Upon completion, click 'Apply' to save your changes.

 


Hidden Article Information

Article Author
Nicole
created 2016-06-24 16:23:04 UTC
updated 2017-05-23 13:37:03 UTC

Labels
automatic database discovery, table value functions, parameters, data joins, table-valued function, views, TVF,
Have more questions? Submit a request