Inline table valued functions - Part 31

In Part 30 of this video 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 functionCREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)RETURNS TABLEASRETURN (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 TABLEASRETURN (Select Id, Name, DateOfBirth, Gender, DepartmentIdfrom tblEmployeeswhere Gender = @Gender)If you look at the way we implemented this function, it is very similar to SCALAR function, with the following differences1. We specify TABLE as the return type, instead of any scalar data type2. 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 functionSelect * 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 functions1. 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 tableSelect Name, Gender, DepartmentName from fn_EmployeesByGender('Male') EJoin tblDepartment D on D.Id = E.DepartmentId