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.

About these ads

3 Responses to “SQL Server Multi-Statement Table-valued UDFs (User Defined Functions)”

  1. sandz Says:

    the code is simple and easy to understand for the beginners

  2. Libin Says:

    Please explain the complex logic using MFUDF.

  3. Libin Says:

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: