Types of UDF

We three types of user defined functions.

1. Scalar Function
User defined scalar function also returns single value as a result of actions perform by function.

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

2. 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.In-line UDFs return a single row or multiple rows and can contain a single SELECT statement.

 --Create function to get employees
Create function fnGetEmployee()
returns Table
As
 return (Select * from Employee)

3. Multi-Statement Table-Valued 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.

The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

 --Create function for EmpID,FirstName and Salary of Employee
Create function fnGetMulEmployee()
returns @Emp Table
(
EmpID int,
FirstName varchar(50),
Salary int
)
As
begin
 Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
--Now update salary of first employee
 update @Emp set Salary=25000 where EmpID=1;
--It will update only in @Emp table not in Original Employee table
return
end 

Source