SQL: User Defined Functions - Multi Statement Table Valued

Nearly finished this ad-hoc series of articles. This started as an exploration of CROSS APPLY and took me back to using user defined functions and how to use functions that return tables.

Our Scalar user defined function returned a single value, but we could have complex code. This is akin to a stored procedure, with the change that we could use the value returned within our SQL. The inline table valued function gave us the ability to return a results set that we could use within our FROM clauses. It was similar to a view (and could be used like a view, in that it could be updateable as well - depending on the SQL).

This type - multi statement table valued - of function creates a table variable which we then populate within our function and return it. We can put additional code within our function rather than a single SELECT statement. So now we can return a complex result set - and use this within our SQL. To be able to do this without functions, you would use a stored procedure and create a table variable (or a temporary table) and execute the stored procedure.

I always like a solution to solve, rather than showing some syntax. So I'd like a function that returns for a particular employee (in Northwind again) the employees formatted name, their managers name and the number of people that reports to the employee.

The first part - the employee name and manager name can be found with this SQL

SELECT 
        E.EmployeeID, 
        E.TitleOfCourtesy + ' ' + E.FirstName + ' ' + E.LastName,
        M.TitleOfCourtesy + ' ' + M.FirstName + ' ' + M.LastName AS ManagerName
    FROM Employees E
    LEFT OUTER JOIN Employees M ON (E.ReportsTo = M.EmployeeID)
    WHERE E.EmployeeID=2

And then to get the number of reports, we can use this

SELECT COUNT(*)
    FROM Employees
    WHERE ReportsTo = 2

It is possible to do this as one query (as a sub-query), but for the sake of this example we are going to perform both SQL within this Multi Statement Table Valued User Defined function (try saying that after a few beers!).

As a function this is what we would write

CREATE FUNCTION EmployeeInfo(@EmployeeID int)
RETURNS @EmployeeDetails TABLE
   (EmployeeID int,
    EmployeeName nvarchar(20),
    ManagerName nvarchar(20),
    NumberOfReports int
   )
AS
BEGIN
    INSERT INTO @EmployeeDetails
        SELECT 
            E.EmployeeID, 
            E.TitleOfCourtesy + ' ' + E.FirstName + ' ' + E.LastName,
            M.TitleOfCourtesy + ' ' + M.FirstName + ' ' + M.LastName AS ManagerName,
            NULL
        FROM Employees E
        LEFT OUTER JOIN Employees M ON (E.ReportsTo = M.EmployeeID)
        WHERE E.EmployeeID=@EmployeeID

    DECLARE @NumberOfReports int

    SELECT @NumberOfReports = COUNT(*)
    FROM Employees
    WHERE ReportsTo = @EmployeeID

    UPDATE @EmployeeDetails SET NumberOfReports = @NumberOfReports
    RETURN
END

This can be executed with

SELECT * FROM dbo.EmployeeInfo(5)