Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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 function
CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType..., @ParamN DataType)
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))
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')


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

New to joins in sql server. Please check the videos below
Part 12 - Basic Joins
Part 13 - Advanced Joins
Part 14 - Self Joins

1 comment:

  1. Below, please find the SQL Query for creating the tblEmployees table.

    CREATE TABLE tblEmployees
    Name NVARCHAR(50),
    DateOfBirth DATETIME,
    Gender NVARCHAR(50),
    DepartmentId INT

    INSERT INTO tblEmployees
    VALUES ('Sam', '1980-12-30', 'Male', 1)

    INSERT INTO tblEmployees
    VALUES ('Pam', '1982-09-01 12:02:36.260', 'Female', 2)

    INSERT INTO tblEmployees
    VALUES ('John', '1985-08-22 12:03:30.370', 'Male', 1)

    INSERT INTO tblEmployees
    VALUES ('Sara', '1979-11-29 12:59:30.670', 'Female', 3)

    INSERT INTO tblEmployees
    VALUES ('Todd', '1978-11-29 12:59:30.670', 'Male', 1)


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.