In this article we will learn about Types of User Define Function in SQL Server with different examples. Let’s start
Contents
Types of User Define Function Introduction
SQL functions are simply sub-programs, which are commonly used and reused in SQL databases to process or manipulate data in SQL quires or stored procedures. SQL functions are small programs that may have zero or more input parameters but can return only one value. SQL server divided into three types of User Define Function.
- Scalar Function
- Inline Table Value Function
- Multi-Statement Table Value Function
Better to read the previous topic to get good knowledge about SQL.
JOIN in SQL
Stored Procedure in SQL
Function in SQL
We will look into each types of function in SQL Server on the below sections.
Scalar Function
It may or may not have parameters but always return a single(scalar) value. The returned value can be of any datatype, except text, ntext, image, cursor and timestamp.
Syntax
CREATE FUNCTION function_name (parameters)
RETURNS data_type AS
BEGIN
–SQL statements
RETURN value
END;
We have already seen the best user define function example on our previous post, So please check on this link.
We will create a Scalar function in SQL Server to get the total number of employee based input department id. Let’s see the below example.
CREATE FUNCTION getTotalEmployeebyDepartment(@DEPTID INT)
RETURNS INT AS
BEGIN
DECLARE @TOTALEMPLOYEE INT
SELECT @TOTALEMPLOYEE= COUNT(*) FROM EMPLOYEE
WHERE DEPTID=DEPTID
RETURN @TOTALEMPLOYEE
END;
we can call the above getTotalEmployeebyDepartment function as below
select dbo.getTotalEmployeebyDepartment(1) 'Total Employee By Department ID'
Inline Table Value Function
- This user define function return a table
- We specify table as the return type, instead of any scalar datatype.
- It is not enclosed between begin and end block
- The structure of the table that gets returned and determined by the select statement with in the function.
Syntax
CREATE FUNCTION function_name (parameters)
RETURNS Table
AS
RETURN (DQL statements)
Important Note
It can be used to achieve the functionality of parameterized views.
The table returned by table valued function, can also be used in join with another table.
Now, It’s time to create an Inline Table Value Function in SQL Server.
CREATE FUNCTION getFNEmployeeDetails(@deptID int)
RETURNS Table
AS
RETURN select * from EMPLOYEE where DeptID = @deptID
We will call the above getFNEmployeeDetails function in SQL Server.
select * from dbo.getFNEmployeeDetails(1)
Multi Statement Table Valued Function
Multi Statement table valued function, We can specify the structure of the table that gets returned.
Syntax
CREATE FUNCTION function_name (parameters)
RETURNS table_variable_name Table_Structure
AS
BEGIN
Assign value to table_variable_name
RETURN table_variable_name
END
We will see the below example to understand more details about Multi Statement table valued user define function in SQL Server.
CREATE FUNCTION GetEmpNameWithDepartmentDetails ()
RETURNS @EmpDetails TABLE
(EName varchar(50), DName varchar(50),
DLocation varchar(50))
AS
BEGIN
INSERT INTO @EmpDetails
SELECT e.NAME, d.NAME, d.LOCATION
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.DeptID = d.ID
RETURN
END
After execute the above script, it will create user define function name of GetEmpNameWithDepartmentDetails in our SQL Server.
For example, we can call the above GetEmpNameWithDepartmentDetails function as below
select * from dbo.GetEmpNameWithDepartmentDetails()
Inline Table Valued Function vs Multi Statement Table Valued Function
| Inline Table Valued Function | Multi Statement Table Valued Function |
|
|
Deterministic Function
Deterministic function always return the same result anytime they are called with a specific set of input values and given the same state of the database.
Example
Square(), Sum(), Power() etc
Non-Deterministic Function
Non-Deterministic function may return the different result each time they are called with a specific set of input values even if the database state that they access remains the same.
Example
Getdate()
Rand() function is Non-Deterministic but if you provide the seed value the function becomes deterministic.
Reference Link
Function in SQL Server
