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)
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
New to joins in sql server. Please check the videos below
Part 12 - Basic Joins
Part 13 - Advanced Joins
Part 14 - Self Joins
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
New to joins in sql server. Please check the videos below
Part 12 - Basic Joins
Part 13 - Advanced Joins
Part 14 - Self Joins
Below, please find the SQL Query for creating the tblEmployees table.
ReplyDeleteCREATE TABLE tblEmployees
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
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)
can we update this function when we joining multiple tables and if yes will that result will give correct output
ReplyDelete