There’s a special variation of the Table-valued UDF called the multi-statement table-value. As with the regular Table-Value, it can only return a single table. However, you can create a table on the fly, populate it, then return the results. Lets take a look at an example, based on our sample from yesterday.
create function dbo.f_LotsOfPeople(@lastNameA as nvarchar(50), @lastNameB as nvarchar(50))
returns @ManyPeople table
(PersonID int, FullName nvarchar(101), PhoneNumber nvarchar(25))
as
begin
insert @ManyPeople (PersonID, FullName, PhoneNumber)
select ContactID
, FirstName + ‘ ‘ + LastName
, Phone
from Person.Contact
where LastName like (@lastNameA + ‘%’);
insert @ManyPeople (PersonID, FullName, PhoneNumber)
select ContactID
, FirstName + ‘ ‘ + LastName
, Phone
from Person.Contact
where LastName like (@lastNameB + ‘%’);
return
end
The first line declares the function and passes in two parameters. The next line declares the return type will be a table, named @ManyPeople. We need to give our table a name, as we’ll be using it inside the function.
The third line defines the layout of our in memory table, @ManyPeople. I’ve declared three columns, and given their names and data type. Next comes the as, followed by a begin/end construct to house our code.
Inside the code I add data to the @ManyPeople table by using traditional Insert syntax. Please note this isn’t quite like a scalar function, I’m restricted to statements that insert or update records in the @ManyPeople table. Complex calculations, etc are restricted unless they take the form of inserting / updating into our table.
To end the function I have to have a Return statement that will end the function and return the @ManyPeople table to the calling routine. And how do we call it? Pretty simple, just like we did yesterday.
select * from dbo.f_LotsOfPeople(‘Abe’, ‘Zie’)
| PersonID | FullName | PhoneNumber |
| ———– | —————- | ————- |
| 2 | Catherine Abel | 747-555-0171 |
| 3 | Kim Abercrombie | 334-555-0137 |
| 1212 | Kim Abercrombie | 208-555-0114 |
| 1370 | Kim Abercrombie | 919-555-0100 |
| 988 | Arvid Ziegler | 398-555-0100 |
(5 row(s) affected)
Multi-Statement Table-valued UDFs can be handy when you need to assemble data from multiple places and return a single table.
Arcane Code 
the code is simple and easy to understand for the beginners
Please explain the complex logic using MFUDF.
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/
AS
BEGIN DECLARE
@RowsAdded int — table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0)
— initialize @Reports with direct reports of the given
employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount
— While new employees were added in the previous iteration
WHILE @RowsAdded > 0 BEGIN
/*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/
UPDATE @reports SET processed = 1 WHERE processed = 0
— Insert employees who report to employees marked 1.
INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid e.empid and r.processed = 1 SET @RowsAdded = @@rowcount
*Mark all employee records whose direct reports have been found in this iteration.*/
UPDATE @reports SET processed = 2 WHERE processed = 1 END
— copy to the result of the function the required columns
INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO
SELECT * FROM fn_FindReports(‘11234’)
but unfortunately it shows some syntax error, pls help me