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):
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.