Kinds of Table-valued functions:

• Inline table-valued function
• Multistatement table-valued function
• Scalar Function

Inline Table-Valued Function

An inline table-valued function returns a variable of data type table whose value is derived from a single Select statement. An inline function does not contain a function body within the Begin and End statement.

For example, We want to see the records of those students which course duration is 3. An Inline function that accepts duration as a parameter and returns all the records that have a duration greater than or equal the parameter value as shown below:

Query

Create Function Fun (@Duration int)
Returns Table       
As
Return
(Select S.id,S.Name,S.Age,C.Course,C.Duration from StudentDetail S  join CourseDetail C on S.Id=C.Id where C.Duration>=@Duration
)

Executing “Fun” function with parameter

Query

Select * from Fun(3)

Output

Multistatement Table-Valued Function

The Multi statement function is slightly complicated from the other two types of functions. A multi-statement function uses multiple statements to build the table that is returned to the calling statement. The function body contains a Begin and End block.

For example, We want to see that records of those students which  is greater than or equal to 21. A Multi statement function that accepts age as a parameter and returns all the records that have age greater than or equal the parameter value as shown below:

Query

Create Function Multistate(@Age varchar(5))
Returns @table table
(
      StudentId varchar(5),
      StudentName varchar(20),
      StudentAge int,
      StudentCity varchar(20),
      StudentState varchar(20)
)
      As
      Begin
      Insert @table
      Select * from StudentDetail where Age>=@Age
      Return
      End
      Select * from Multistate(21)

Executing “Multistate” function with parameter

Query

Scalar Function

A Scalar function returns a single value of the data type referenced in the return clause of the creates function statement.

For example: In scalar function executing while loop when Num1 value  will reach on 100 than  loop will be terminate and Num2 value adds with Num1 and return the total of Num1 and Num2 as a result as shown below:

Query

Create Function SumTwoValues
( @Num1 int, @Num2 int )
 Returns int
 As
Begin
 While @Num1 <100
  Begin
   Set @Num1 =@Num1 +1
  End
  Return (@Num1+@Num2)
End

Executing the “SumTwoValues” function with two-parameter.

Query