A table-valued function is a user define function can be used where views or table expression are allowed in T-SQL.User define function contain additional feature while views limited with single select statement. Table- valued function return the output as a table data type. The table data type is a special data type is used to store a set of rows. A user defined function works in the same way as a system function.

Kinds of Table-valued functions:
  • ·         Inline table-valued function
  • ·         Multistatement table-valued function
  • ·         Scalar Function
Inline Table-Valued Function

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

For example: We want to see that 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 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

Table-Valued Functions in SQL Server

Multistatement Table-Valued Function

The Multistatement function is slightly complicated from other two types of function. A multistatement 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 .An Multistatement 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

Table-Valued Functions in SQL Server

Scalar Function

A Scalar function return single value of the data type referenced in return clause of the create 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 add with Num1 and return total of Num1 and Num2 as 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 “SumTwoValues” function with two parameter.

Query

Table-Valued Functions in SQL Server


  Modified On Nov-30-2017 02:51:00 AM

Leave Comment