We will see how to write Table valued functions in SQL SERVER.
Functions in sql server are of two types. Scalar Functions and Table Valued functions.
Scalar Functions return single value.
where as table Valued Functions return table as an output.
Lets see how to create a Table Valued Functi on.
1)Create a sample table "StudentDtl" as follows:
CREATE TABLE StudentDtl
(
Name VARCHAR(100),
RollNo INT,
CourseEnrolled VARCHAR(100),
Address VARCHAR(1000)
)
2)Insert data into the table.
INSERT INTO StudentDtl VALUES('Raj',1,'CSE','Delhi')
INSERT INTO StudentDtl VALUES('Ram',2,'CSE','Delhi')
INSERT INTO StudentDtl VALUES('Sam',3,'CSE','Delhi')
INSERT INTO StudentDtl VALUES('Roy',4,'ECE','Delhi')
INSERT INTO StudentDtl VALUES('Reeta',5,'EEE','Delhi')
INSERT INTO StudentDtl VALUES('Sita',6,'EEE','Delhi')
3)Create Table Valued Function which return Student Name , RollNo and Address based on CourseName.
CREATE FUNCTION FetchStudents_CourseWise
(
@CourseName VARCHAR(100) = null
)
RETURNS @StudentDetails TABLE
(
Name VARCHAR(100),
RollNo INT,
Address VARCHAR(1000)
)
WITH EXECUTE AS CALLER
AS
BEGIN
INSERT INTO @StudentDetails
SELECT Name , RollNo, [Address] FROM StudentDtl WHERE CourseEnrolled = @CourseName
RETURN ;
END
GO
4)Execute the function
SELECT * FROM FetchStudents_CourseWise('CSE') Output: Name RollNo Address Raj 1 Delhi
Ram 2 Delhi
Sam 3 Delhi