Windows Azure SQL Database Procedures and Functions - Cloud Database Development and Management - page 254

and class id. hen, based on the inserted student id, the function inds the student's
classmates in each class he/she is enrolled. Enter the following code in New Query .

CREATE FUNCTION fn_StudentInfo (@Sid int)
RETURNS @INFO TABLE (StudentID int, LastName char(20) NOT NULL,
ClassID int)
AS
BEGIN
INSERT @INFO
SELECT S.StudentID, S.LastName, C.ClassID
FROM STUDENT S, CLASS C, STUDENT_CLASS T
WHERE S.StudentID = @Sid AND S.StudentID = T.StudentID
AND T.ClassID = C.ClassID
INSERT @INFO
SELECT S.StudentID, S.LastName, C.ClassID
FROM STUDENT S, @INFO I, CLASS C, STUDENT_CLASS T
WHERE I.ClassID = C.ClassID AND T.ClassID = C.ClassID AND
T.StudentID = S.StudentID AND S.StudentID <> @Sid
RETURN
END

2. Highlight the code and click Run . As shown in Figure 7.21, the function fn_
StudentInfo is successfully created.

3. To see how the function works, enter the following SQL statement:

SELECT * FROM fn_StudentInfo(11)

Highlight the code and click Run . Figure 7.22 shows that the function returns the last
names and ids of the students who are the classmates of the student with the id 11.

Figure 7.21 Function fn_StudentInfo.