Database Reference
In-Depth Information
Figure 7.3
Create and use user-deined inline table function.
functions can be categorized into two types:
inline function
and
multistatement function
. For
an inline table function, the returned result set is generated by a single SELECT statement. In
a multistatement table function, a TABLE variable is used to store the rows returned from the
multiple SELECT statements.
To illustrate the use of inline table functions, let us consider a user-deined table function
that returns a result set that contains the information about a group of students who are enrolled
in the same class that teaches VB. he function and its application in a query are illustrated in
F i g u r e 7. 3 .
he function fnClassmate returns a table constructed from four tables: STUDENT,
STUDENT_CLASS, CLASS, and COURSE. he keyword RETURNS indicates the returned
data type is TABLE. here is no need of BREAK. . .END since there is only one query in the code
block. Notice the diference between the keywords RETURNS and RETURN. RETURN is used
to exit the function unconditionally after the query is executed. As shown in Figure 7.3, the func-
tion is used as a table in the FROM clause.
To illustrate the use of a multistatement function, consider the example that returns informa-
tion about a given student and his/her classmates. he diference between this example and the
previous example is that the function in this example uses a student id as the input parameter. hat
is, at the beginning, we do not know which class the student is enrolled in. In this function, we need
to irst query the class information based on the given student id. hen, we will query the classmate
information based on the class id selected from the previous SQL statement. A multistatement table
function can be used to accomplish this task. he example is displayed in Figure 7.4.
Another diference between an inline function and a multistatement function is that the
returned table variable in a multistatement function must be explicitly deined. @INFO is the
returned table variable explicitly deined in Figure 7.4. he BEGIN. . .END keywords enclose two
SQL statements. he irst one inserts information about a student, his/her class(es), and course(s)
into the table variable @INFO based on the input parameter @Sid. he second INSERT statement
inserts information about the students and classes based on the class ids inserted by the previous
INSERT statement. he condition S.StudentID
<>
I.StudentID is used to avoid the duplicated
row about the student already inserted by irst INSERT statement.
To view the result, call the function fnClassmateInfo in a FROM clause shown in Figure 7.5.
