---

Multi statement table valued functions are very similar to Inline Table valued functions, with a few differences. Let's look at an example, and then note the differences.

We have a Employee table like below.

Id	Name	DateOfBirth	Gender	DepartmentId
1	Rambo	1980-12-30 00:00:00.000	Male	1
2	Roma	1982-09-01 12:02:36.260	Female	2
3	Inza	1985-08-22 12:03:30.370	Male	1
4	Sara	1979-11-29 12:59:30.670	Female	3
5	Azam	1978-11-29 12:59:30.670	Male	1

Let's write an Inline and multi-statement Table Valued functions that can return the output shown below.

Id	Name	DOB
1	Rambo	1980-12-30
2	Roma	1982-09-01
3	Inza	1985-08-22
4	Sara	1979-11-29
5	Azam	1978-11-29

Inline Table Valued function(ILTVF):

Create Function fn_ILTVF_GetEmployees()
Returns Table
as
Return (Select Id, Name, Cast(DateOfBirth as Date) as DOB
        From Employees)

Multi-statement Table Valued function(MSTVF):

Create Function fn_MSTVF_GetEmployees()
Returns @Table Table (Id int, Name nvarchar(20), DOB Date)
as
Begin
 Insert into @Table
 Select Id, Name, Cast(DateOfBirth as Date)
 From Employees
 
 Return
End

Calling the Inline Table Valued Function:

Select * from fn_ILTVF_GetEmployees()

Calling the Multi-statement Table Valued Function:

Select * from fn_MSTVF_GetEmployees()

Now let's understand the differences between Inline Table Valued functions and Multi-statement Table Valued functions

• In an Inline Table Valued function, the RETURNS clause cannot contain the structure of the table, the function returns. Where as, with the multi-statement table valued function, we specify the structure of the table that gets returned
• Inline Table Valued function cannot have BEGIN and END block, where as the multi-statement function can have.
• Inline Table valued functions are better for performance, than multi-statement table valued functions. If the given task, can be achieved using an inline table valued function, always prefer to use them, over multi-statement table valued functions.
• It's possible to update the underlying table, using an inline table valued function, but not possible using multi-statement table valued function.
  
  

Updating the underlying table using inline table valued function: 
This query will change Rambo to Rabo, in the underlying table Employees. When you try do the same thing with the multi-statement table valued function, you will get an error stating 'Object 'fn_MSTVF_GetEmployees' cannot be modified.'
Update fn_ILTVF_GetEmployees() set Name='Rabo' Where Id = 1

Reason for improved performance of an inline table valued function:
Internally, SQL Server treats an inline table valued function much like it would a view and treats a multi-statement table valued function similar to how it would a stored procedure.

Prerequisite Code:

 CREATE TABLE Employees
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
Name NVARCHAR(50),
DateOfBirth DATETIME,
Gender NVARCHAR(50),
DepartmentId INT
);

INSERT INTO Employees
VALUES ('Rambo', '1980-12-30', 'Male', 1)

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

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

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

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

Select * from  Employees

---