What Is Function In SQL Server and How To Use It

What Is Function In SQL Server and How To Use It

In this tutorial i will let you know What Is Function In SQL Server and How To Use It.Function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, and returns the resulte.The return value can either be a scalar (single) value or a table.

Types of Function:

System Defined Function:

Scalar Function: Scalar functions operates on a single value and returns a single value.

Example:

Scalar Function	Description
abs(-1.7)	Returns absolute number of the given number means 1.7.
rand(110)	This will generate random number of 110 characters.
upper(‘sql’)	Returns upper case of given string means ‘SQL’
lower(‘SQL’)	Returns lower case of given string means ‘sql’
ltrim(‘ sql’)	This will remove the spaces from left hand side of ‘sql’ string.
convert(int, 55.76)	This will convert the given float value to integer means 55.

Aggregate Function: Aggregate functions operates on a collection of values and returns a single value.

Example:

Aggregate Function	Description
max()	Returns maximum value from a collection of values.
min()	Returns minimum value from a collection of values.
average()	Returns average of all values in a collection.
count()	Returns no of counts from a collection of values.

---

User Defined Function:

Scalar Function: User defined scalar function also returns single value as a result. We return any datatype value from this function.

Example:

Create function fn_name
(
@FirstName varchar(50),
@LastName varchar(50)
)
returns varchar(100)
As
Begin return (Select @FirstName + ''+ @LastName);
end 

go 

select dbo.fn_name(firstname,lastname) as Name from hr.emp

Inline Table-Valued Function: User defined inline table-valued function returns a table variable as a result. The value of table variable should be derived from a single SELECT statement.

Example:

Create function tvf_table()
returns Table
As
return (Select * from hr.emp)

go

select * from tvf_table()

Multi-Statement Table-Valued Function: User defined multi-statement table-valued function returns a table variable as a result. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

Example:

Create function fn_mstvf()
returns @Emp Table
(
EmpID int, 
FirstName varchar(50),
lastname varchar(50)
)
As
begin
Insert @Emp Select h.EmpID,h.FirstName,h.lastname from hr.emp h;

update @Emp set lastname='Singh' where EmpID=1;
return
end

go

select * from fn_mstvf()