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:
Create Function Fun (@Duration int)
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
Select * from Fun(3)
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:
Create Function Multistate(@Age varchar(5))
Returns @table table
Select * from StudentDetail where Age>=@Age
Select * from Multistate(21)
Executing “Multistate” function with parameter
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:
Create Function SumTwoValues
( @Num1 int, @Num2 int )
While @Num1 <100
Set @Num1 =@Num1 +1
Executing “SumTwoValues” function with two parameter.