Multi-Statement Table Valued Function in SQL Server
In this article, I will discuss the Multi-Statement Table Valued Function in SQL Server with Examples. Please read the below articles before proceeding to this article.
The Multi-Statement Table Valued Function in SQL Server is the same as the Inline Table values Function in SQL Server means it also returns a table as an output but with the following differences.
- Multi-Statement Table-Valued Function body can contain more than one statement.
- The structure of the table is returned from the function that can be defined by us.
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 following Department and Employee tables.
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.
-- 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?
- 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 the multi-statement table-valued function we can define the structure of the table that the function is going to return.
- The inline table value function cannot have BEGIN and END blocks whereas the multi-statement function can have the Begin and End blocks.
- Inline table-valued functions are better for performance than the 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.
- 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.
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?
- A function must return a value whereas a procedure never returns a value.
- The procedure can have parameters of both input and output whereas a function can have only input parameters.
- 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.
- A procedure provides the options to perform transaction management, error handling, etc whereas these operations are not possible in a function.
- We call a procedure using EXECUTE/ EXEC command whereas a function is called by using SELECT command only.
- From a procedure, we can call another procedure or a function whereas from a function we can call another function but not a procedure.
- 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 am going to discuss the use of Encryption and schema Binding Options in SQL Server Functions with examples. Here, 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.