SQL Server Multi-Statement Table-valued UDFs (User Defined Functions)

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.