When we want to return table as a result, then we
can use table valued functions. User defined Table Valued functions returns table variable
as a result. There are two types of table value functions:
1. Inline
Table Valued: Returns
table variable as a result.
Create function fnGetStudent()
returns Table
As
return (Select * from Student_info )
Execute Function:
select * from fnGetStudent()
Output:
|
2. Multi- statement Table valued UDF:
When
we want to get data from multiple tables or statements we use Multi- statement
Table valued function. It returns table variable as result of action performed
by function. Table must be declared and defined explicitly. Value of table
variable will be derived from multiple statements.
we can also pass parameters in function, which can we use as filter or condition.
Create function fnGetMULStudent()
returns @Student Table
(
Student_Id int,
Name varchar(100),
class varchar(100)
)
As
Begin
insert into @Student select s.Student_id, s.Student_name,S.class from Student_info
s
update @Student set class='MPhill' where Student_id=6
return
end
Execute Function:
select * from fnGetMULStudent()
Output:
|
