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()
