Table-Valued Functions

for RuBoard

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.

for RuBoard