SQL SERVER - Table Valued Function

Sailajareddy
Posted by Sailajareddy under Others category on | Points: 40 | Views : 1720
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

Comments or Responses

Login to post response