The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Table-Valued Functions

Table-valued functions are also quite powerful and relatively easy to use. Table-valued functions return a table data type as their function result (UDFs do not support output parameters). With a few exceptions, you can perform the same operations on this table that you can perform on a regular table, including inserting, deleting, and updating (you can't use INSERT…EXEC or use user-defined data types). Text columns are automatically flagged as text in row columns. Here's an example of a multistatement table-valued UDF (Listing 10-2):

Listing 10-2 A basic table-valued function.

CREATE TABLE staff (employee int PRIMARY KEY, employee_name varchar(10),
supervisor int NULL REFERENCES staff (employee))
INSERT staff VALUES (1,'GROUCHO',1)
INSERT staff VALUES (2,'CHICO',1)
INSERT staff VALUES (3,'HARPO',2)
INSERT staff VALUES (4,'ZEPPO',2)
INSERT staff VALUES (5,'MOE',1)
INSERT staff VALUES (6,'LARRY',5)
INSERT staff VALUES (7,'CURLY',5)
INSERT staff VALUES (8,'SHEMP',5)
INSERT staff VALUES (9,'JOE',8)
INSERT staff VALUES (10,'CURLY JOE',9)
GO
DROP FUNCTION dbo.ORGTABLE
GO
CREATE FUNCTION dbo.ORGTABLE(@employee_name varchar(10)='%')
RETURNS @orgtable TABLE (sequence int,
supervisor varchar(10),
supervises varchar(10),
employee_name varchar(10))
AS
BEGIN
DECLARE @worktable TABLE (seq int identity,
chartdepth int,
employee int,
supervisor int)
INSERT @worktable (chartdepth, employee, supervisor)
SELECT chartdepth=1, employee=o2.employee, supervisor=o1.employee
FROM staff o1 INNER JOIN staff o2 ON (o1.employee=o2.supervisor)
WHERE o1.employee_name LIKE @employee_name
WHILE (@@rowcount > 0) BEGIN
INSERT @worktable (chartdepth, employee, supervisor)
SELECT DISTINCT o1.chartdepth+1, o2.employee, o1.supervisor
FROM @worktable o1 INNER JOIN @worktable o2 ON
(o1.employee=o2.supervisor)
WHERE o1.chartdepth=(SELECT MAX(chartdepth) FROM @worktable)
AND o1.supervisor<>o1.employee
END
INSERT @orgtable
SELECT seq, s.employee_name, supervises='supervises', e.employee_name
FROM @worktable o INNER JOIN staff s ON (o.supervisor=s.employee)
INNER JOIN staff e ON (o.employee=e.employee)
WHERE o.supervisor<>o.employee
ORDER BY seq
RETURN
END
GO
SELECT * FROM ORGTABLE(DEFAULT) ORDER BY Sequence
GO
DROP TABLE staff

(Results)

sequence    supervisor supervises employee_name
----------- ---------- ---------- -------------
2           GROUCHO    supervises CHICO
3           CHICO      supervises HARPO
4           CHICO      supervises ZEPPO
5           GROUCHO    supervises MOE
6           MOE        supervises LARRY
7           MOE        supervises CURLY
8           MOE        supervises SHEMP
9           SHEMP      supervises JOE
10          JOE        supervises CURLY JOE
11          GROUCHO    supervises HARPO
12          GROUCHO    supervises ZEPPO
13          GROUCHO    supervises LARRY
14          GROUCHO    supervises CURLY
15          GROUCHO    supervises SHEMP
16          MOE        supervises JOE
17          SHEMP      supervises CURLY JOE
18          GROUCHO    supervises JOE
19          GROUCHO    supervises CURLY JOE
20          MOE        supervises CURLY JOE
21          GROUCHO    supervises CURLY JOE

This routine creates an organizational chart from the staff table, then returns it as the function result. Note the use of an identity column on the internal worktable and the insert and joins with it later in the routine. From a usage standpoint, table variables are nearly identical to temporary tables, but they're far more scalable in terms of concurrency.

Note the absence of the owner qualifier on the call to the function. Only scalar functions require owner qualification; table-valued functions and inline functions do not.

The routine ends with a RETURN statementa requirement of all UDFs. Because the table variable has already been loaded with data, the end result is that this table is returned to the caller.