SQL Server Functions with examples

SQL Server Functions :

Functions in SQL Server is a set of SQL statements that accepts only input parameters at run time and perform some actions and return the result, which can be only single value or a table.

Types of Functions:

System Defined Function: 

These functions are defined by SQL Server so these are Pre-defined functions. Like: 

AVG()

COUNT()

Sum()

ABS()  

Round() 

Convert() 

To use system defined function we have to pass parameters information only to get output from these functions.

User Defined Functions: 

These Functions are created by Users in database for performing some operations on data.

Types of User Defined Functions are:

Scalar Function:

User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

Example:

--Create function to get employee full name

Create function UFD_GetEmpFullName

(

 @FirstName varchar(50),

 @LastName varchar(50)

)

returns varchar(101)

As

Begin return (Select @FirstName + ' '+ @LastName);

end

--Calling the above created function

Select dbo.UFD_GetEmpFullName(FirstName,LastName) as Name, Salary from dbo.Employee

Inline Table-Valued Function:

User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

Example:

--Create function to get employees

Create function UFD_GetEmpDetails()

returns Table

As

 return (Select * from dbo.Employee)

 --Call the above created function

Select * from UFD_GetEmpDetails()

Multi-Statement Table-Value Function:

User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple SQL statements.

Example:

--Create function for EmpID,FirstName and Salary of Employee

Create function UFD_GetMulEmployee()

returns @Employee Table

(

EmpID int,

FirstName varchar(50),

Salary int

)

As

begin

 Insert into @Employee Select e.EmpID,e.FirstName,e.Salary from dbo.Employee e;

--Now update salary of first employee

 update @Employee set Salary=25000 where EmpID=1;

--It will update only in @Emp table not in Original Employee table

return

end

 --Call the above created function

Select * from UFD_GetMulEmployee()