Multi Statement Table Valued Function in SQL Server

Multi-Statement Table Valued Function in SQL Server

In this article, I will discuss the Multi-Statement Table Valued Function in SQL Server. Please read below articles before proceeding to this article.

Scalar Function in SQL Server

The Inline Table values Function in SQL Server.

The Multi-Statement Table Valued Function in SQL Server is same as the Inline Table values Function in SQL Server means it also return a table as an output but with the following differences.

  1. Multi-Statement Table-Valued Function body can contain more than one statement.
  2. The structure of the table is returned from the function can be defined by us.

Multi-Statement Table Valued Function in SQL Server

NOTE: In case Multi-Statement Table Valued Function in SQL Server we need to define the structure of the table being return.

Let us understand Multi-Statement Table-Valued Function comparing with the Inline Table Valued Function in SQL Server with an example.

We are going to use the below Department and Employee tables.

Multi-Statement Table Valued Function in SQL Server

Please use below SQL Script to create and populate the Department and Employee tables with some test data.
-- Create Department Table
CREATE TABLE Department
(
  ID INT PRIMARY KEY,
  DepartmentName VARCHAR(50)
)
GO

-- Populate the Department Table with test data
INSERT INTO Department VALUES(1, 'IT')
INSERT INTO Department VALUES(2, 'HR')
INSERT INTO Department VALUES(3, 'Sales')
GO

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT FOREIGN KEY REFERENCES Department(ID) 
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO

Let’s write Inline and multi-statement Table-Valued functions that return the following output.

Multi-Statement Table Valued Function in SQL Server

-- Inline Table Valued function:
CREATE FUNCTION ILTVF_GetEmployees()
RETURNS Table
AS
RETURN (SELECT ID, Name, Cast(DOB AS Date) AS DOB
        FROM Employee)

-- Multi-statement Table Valued function:
CREATE FUNCTION MSTVF_GetEmployees()
RETURNS @Table Table (ID int, Name nvarchar(20), DOB Date)
AS
BEGIN
  INSERT INTO @Table
    SELECT ID, Name, Cast(DOB AS Date)
    FROM Employee
  Return
End

-- Calling the Inline Table Valued Function:
SELECT * FROM ILTVF_GetEmployees()

-- Calling the Multi-statement Table Valued Function:
SELECT * FROM MSTVF_GetEmployees()
What are the differences between Inline and multi-statement Table-valued function?
  1. In an Inline Table Valued Function, the returns clause cannot contain the structure of the table that the function is going to return whereas in multi-statement table valued function we can define the structure of the table that the function is going to return.
  2. The Inline table value function cannot have BEGIN and END blocks whereas the multi-statement function can have the Begin and End blocks.
  3. Inline table-valued functions are better for performance than multi-statement table valued function. So, if the given task can be achieved using an inline table-valued function, then it always preferred to use Inline Table-valued Function over the multi-statement table valued function.

Reason For Better Performance: Internally SQL Server treats an Inline table-valued function much like a view and treats a multi-statement table valued function as a stored procedure.

  1. It is possible to update the underlying database table using the inline table-valued function but it is not possible to update the underlying database table using the multi-statement table valued function.
Example:

SELECT * FROM dbo.ILTVF_GetEmployees()

For the above function, Employee is the underlying database table.

UPDATE ILTVF_GetEmployees() SET Name=’Pranaya1′ WHERE ID= 1

The above update query will change the name Pranaya to Pranaya1, in the underlying table Employee. When we try to do the same thing with the multi-statement table valued function, we will get an error stating ‘Object ‘MSTVF_GetEmployees’ cannot be modified.‘ The reason is that the multi-statement table valued function did not get the data directly from the underlying database table instead it gets the data from the table variable.

What is the difference between functions and procedures?
  1. A function must return a value whereas a procedure never returns a value.
  2. The procedure can have parameters of both input and output whereas a function can have only input parameters.
  3. In a procedure, we can perform Select. Update, insert and delete operations whereas function can only be used to perform select operations. It cannot be used to perform insert, update and delete operations that can change the state of the database.
  4. A procedure provides the options to perform transaction management, error handling, etc whereas these operations are not possible in a function.
  5. We call a procedure using EXECUTE/ EXEC command whereas a function is called by using SELECT command only.
  6. From a procedure, we can call another procedure or a function whereas from a function we can call another function but not a procedure.
  7. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

In the next article, I will discuss the use of Encryption and schema Binding Options in SQL Server Functions with examples.

SUMMARY

In this article, I try to explain the Multi-Statement Table Valued Function in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *