Table Valued Functions
Table of Content:
What is a table-valued function
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use 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)
If you look at the way we implemented this function, it is very similar to SCALAR function, with the following differences
- We specify TABLE as the return type, instead of any scalar data type
- The function body is not enclosed between BEGIN and END block. Inline table valued function body, cannot have BEGIN and END block.
- The structure of the table that gets returned, is determined by the SELECT statement with in the function.
Code: Use this below code to select a specific database
USE TestDatabase
Code: Create this below table to understand function
CREATE TABLE [dbo].[Emp_Master]( [Emp_ID] [nchar](10) NULL, [Emp_Name] [nchar](10) NULL, [Emp_DOB] [date] NULL ) ON [PRIMARY]
Code: Create this below table
CREATE TABLE [dbo].[EmpSalary]( [ID] [nchar](10) NULL, [Name] [nchar](10) NULL, [Salary] [numeric](10, 2) NULL, [Dept] [nchar](10) NULL ) ON [PRIMARY]
Insert some records inside above two table.
Code: use this code to see two table
Select * from EmpSalary Select * from Emp_Master ALTER TABLE Emp_Master ADD Emp_DOB DATE; UPDATE Emp_Master SET Emp_DOB = '12-02-1996' WHERE Emp_ID = 1 UPDATE Emp_Master SET Emp_DOB = '11-02-1996' WHERE Emp_ID = 2 UPDATE Emp_Master SET Emp_DOB = '10-02-1996' WHERE Emp_ID = 3 UPDATE Emp_Master SET Emp_DOB = '09-02-1996' WHERE Emp_ID = 4 UPDATE Emp_Master SET Emp_DOB = '08-02-1996' WHERE Emp_ID = 5
Code: Create a simple function using this below code
/* User Defined Function */ -- Tabled Valued Function CREATE FUNCTION SelectDataEmpSalary ( @ID INT ) RETURNS TABLE AS RETURN SELECT Name, Salary, Dept FROM EmpSalary WHERE ID = @ID; -- Execute Function SELECT * FROM SelectDataEmpSalary(1)
Where can we use Inline Table Valued functions
- Inline Table Valued functions can be used to achieve the functionality of parameterized views. We will talk about views, in view section.
- The table returned by the table valued function, can also be used in joins with other tables.