What are the different types of functions in SQL Server?

Introduction:

In this article i will explain what is Function in SQL Server.

Description:

In previous articles i explained about joins, and Batches. Now i will explain what is Function in SQL Server.

Functions:

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).

Types of Functions:

1. Scalar-Valued Functions
2. Table-Valued Functions
3. System Functions

Scalar-Valued Function:

User defined scalar function will return a single value.

The following example creates a multistatement scalar function. The function takes two input values, and returns a single value.

Example:

Create function Addition(@a int, @b int)
Returns int
Begin

    -- Declare local variables.
    Declare @c int
    Set @c = @a + @b
    
    Return @c
End

How to execute:

Select dbo.Addition(10,20) as Result

Output:

Table-Valued Function:

Basically we can return a table from Table-Valued Function in two ways.

1. Inline Table-Valued Function
2. Multi statement Table-Valued Function

Inline Table-Valued Function:

Example:

Create function EmployeeData()
Returns table
As

    Return (select EmployeeID, EmployeeName, Email from dbo.Employee)

How to execute:

select * from dbo.EmployeeData()

Output:

Multi statement Table-Valued Function:

Example:

Create function GetEmployeesData()
Returns @emp table
(
    EmployeeID int, EmployeeName varchar(50), Email varchar(50)
)
As
Begin
    
    insert into @emp
    select EmployeeID, EmployeeName, Email from dbo.Employee
    return
    
End

How to execute:

select * from dbo.GetEmployeesData()

Output: