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:
- Scalar-Valued Functions
- Table-Valued Functions
- 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.
- Inline Table-Valued Function
- 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:
-
CreatedDec 21, 2013
-
UpdatedNov 03, 2020
-
Views2,033