Table of Contents

    How to Use Multi-Statement Table-Valued Functions in SQL Server: A Detailed Guide

    Rumman Ansari July 21, 2024 6,142 views
    How to Use Multi-Statement Table-Valued Functions in SQL Server: A Detailed Guide

    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