SQL SERVER - Types of User Defined Functions

User Defined Functions in SQL Server are nothing but the extension to the Microsoft  Defined System Functions, Where user can include his own Business logic and his own calculations.

There are 3 types of user defined functions:
They are :
1. Scalar Function
2. Table or Inline function
3. Multi statement Function

Scalar Function : If the user defined function returns any scalar value like integer or character etc then it is called Scalar Function

Example:

Create Function dbo.fn_Returns_Number                      Returns INT as
Begin
  Declare @Num int
  Set @Num = 27
  Return @Num
End

Table valued or Inline Function : If the user defined function returns a table then it is called Table valued or Inline function. Here we will use single Select query in the body of the user defined function to return a set of rows from any specific table.

Example:

Create Function dbo.fn_Returns_Table(@City Varchar(10))
Returns Table as
Return(Select Emp_Name From Employee where City = @City)

Multi Statement Function : If the definition of a Table Valued Function contains the design of the table, then it is called Multi statement function. That means this function allows to specify the design of the table being returned from User defined function.

Example:

Create Function dbo.fn_TableWithDesign(@Date Varchar(10))
Returns @Temp Table(Id Int, Desc varchar(10),Qty Int) as
Begin
    Insert Into @Temp
    Select ProdId,ProdName, ProdQty
    From Product
    where ProdDate > @Date                                   
Return
End