Inline table valued functions - Part 31

In Part 30 of this series we have seen how to create and call 'scalar user defined functions'. In this part of the video series, we will learn about 'Inline Table Valued Functions'.

From Part 30, We learnt that, a scalar function, returns a single value. on the other hand, an Inline Table Valued function, return a table. 

Syntax for creating an inline table valued function
CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
RETURNS TABLE
AS
RETURN (Select_Statement)

Consider this Employees table shown below, which we will be using for our example.



Create a function that returns EMPLOYEES by GENDER.
CREATE FUNCTION fn_EmployeesByGender(@Gender nvarchar(10))
RETURNS TABLE
AS
RETURN (Select Id, Name, DateOfBirth, Gender, DepartmentId
    from tblEmployees
    where Gender = @Gender)

If you look at the way we implemented this function, it is very similar to SCALAR function, with the following differences
1. We specify TABLE as the return type, instead of any scalar data type
2. The function body is not enclosed between BEGIN and END block. Inline table valued function body, cannot have BEGIN and END block.
3. The structure of the table that gets returned, is determined by the SELECT statement with in the function.

Calling the user defined function
Select * from fn_EmployeesByGender('Male')

Output:


As the inline user defined function, is returning a table, issue the select statement against the function, as if you are selecting the data from a TABLE.

Where can we use Inline Table Valued functions
1. Inline Table Valued functions can be used to achieve the functionality of parameterized views. We will talk about views, in a later session.
2. The table returned by the table valued function, can also be used in joins with other tables.

Consider the Departments Table


Joining the Employees returned by the function, with the Departments table
Select Name, Gender, DepartmentName 
from fn_EmployeesByGender('Male') E
Join tblDepartment D on D.Id = E.DepartmentId

Executing the above query should produce the following output