Home > DeveloperSection > Articles > Table-Valued Functions in SQL Server

Table-Valued Functions in SQL Server


Database Database 
Ratings:
1 Comment(s)
 10070  View(s)
Rate this:

Table-Valued Functions in SQL Server

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


Table valued function.

By Awadhendra Tiwari on   5 years ago

Exactly I need this.

Thanks.


Don't want to miss updates? Please click the below button!

Follow MindStick