The following Microsoft SQL Server T-SQL scripts demonstrate how to create and invoke table-valued functions.

-- T-SQL Table-Valued Function QUICK SYNTAX - inline table-valued function

USE AdventureWorks2008;

GO

CREATE FUNCTION tvfnPurchasesByVendor (@VendorID INT)

RETURNS TABLE

AS

  RETURN

    (SELECT   P.ProductID,

              P.Name,

              SUM(POD.LineTotal) AS [YTD Total]

     FROM     Production.Product AS P

              INNER JOIN Purchasing.PurchaseOrderDetail AS POD

                ON POD.ProductID = P.ProductID

              INNER JOIN Purchasing.PurchaseOrderHeader AS POH

                ON POH.PurchaseOrderID = POD.PurchaseOrderID

     WHERE    POH.VendorID = @VendorID

     GROUP BY P.ProductID, P.Name);

GO

-- SQL Server apply / execute / call table-valued function

SELECT * FROM  tvfnPurchasesByVendor (1538)

/*    ProductID   Name              YTD Total

      928         LL Mountain Tire  886751.25

      929         ML Mountain Tire  1005427.50  */

------------

-- CROSS APPLY usage with system table-valued function - DMF

SELECT TOP 10

          DBName        = DB_NAME(eqt.dbid)

         ,TotalReads    = SUM(total_logical_reads)

         ,ExecuteCount  = SUM(eqs.execution_count)

FROM sys.dm_exec_query_stats eqs

  CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) as eqt

GROUP BY DB_NAME(eqt.dbid)

ORDER BY ExecuteCount DESC;

------------

-- SQL Server create table-valued function - user-defined function

USE AdventureWorks;

GO

CREATE FUNCTION dbo.tvfnListStaffOfSupervisor  (@SuperVisorID INT)

RETURNS @StaffListing TABLE(EmployeeID   INT,

                            FullName     NVARCHAR(128),

                            Title        NVARCHAR(64),

                            OrgLevel     INT,

                            OrgChartSort NVARCHAR(128))

AS

  BEGIN

    -- Recursive CTE - Common Table Expression

    WITH ImmediateSupervisor(Name,Title,EmployeeID,OrgLevel,Sorting)

         AS (SELECT CAST(c.FirstName + ' ' + c.LastName AS NVARCHAR(128)),

                    e.Title,

                    e.EmployeeID,

                    0,

                    CAST(c.FirstName + ' ' + c.LastName  AS NVARCHAR(128))

             FROM   HumanResources.Employee AS e

                    INNER JOIN Person.Contact AS c

                      ON e.ContactID = c.ContactID

             WHERE  e.EmployeeID = @SuperVisorID

             UNION ALL

  SELECT  CAST(REPLICATE('> ',OrgLevel+1)+c.FirstName+' '+c.LastName AS NVARCHAR(128)),

                    e.Title,

                    e.EmployeeID,

                    OrgLevel + 1,

  CAST(RTRIM(Sorting) + '> ' + FirstName + ' ' + LastName  AS NVARCHAR(128))

             FROM   HumanResources.Employee AS e

                    INNER JOIN Person.Contact AS c

                      ON e.ContactID = c.ContactID

                    INNER JOIN ImmediateSupervisor AS s

                      ON e.ManagerID = s.EmployeeID)

    INSERT @StaffListing

    SELECT EmployeeID,

           Name,

           Title,

           OrgLevel,

           Sorting

    FROM   ImmediateSupervisor

    RETURN

  END;

GO

-- Example for table-valued function call / execution / invocation

SELECT OrgLevel,

       FullName,

       EmployeeID,

       Title

FROM     dbo.tvfnListStaffOfSupervisor(12)

ORDER BY OrgChartSort;

/*

OrgLevel    FullName          EmployeeID  Title

0           Terri Duffy             12    Vice President of Engineering

1           > Roberto Tamburello    3     Engineering Manager

2           > > Dylan Miller        158   Research and Development Manager

3           > > > Diane Margheim    79    Research and Development Engineer

3           > > > Gigi Matthew      114   Research and Development Engineer

3           > > > Michael Raheem    217   Research and Development Manager

2           > > Gail Erickson       9     Design Engineer

2           > > Jossef Goldberg     11    Design Engineer

2           > > Michael Sullivan    267   Senior Design Engineer

2           > > Ovidiu Cracium      263   Senior Tool Designer

3           > > > Janice Galvin     265   Tool Designer

3           > > > Thierry D'Hers    5     Tool Designer

2           > > Rob Walters         4     Senior Tool Designer

2           > > Sharon Salavaria    270   Design Engineer  */

------------

-- T-SQL table-valued function - user-defined function - udf

USE pubs

GO

CREATE FUNCTION fnSalesByAuthorForState

               (@State CHAR(2))

RETURNS TABLE

AS

  RETURN

    (SELECT   TOP ( 2 ^ 32 ) [State] = @State,

                             SSN = a.au_id,

                             FirstName = a.au_fname,

                             LastName = a.au_lname,

                             QtySold = SUM(s.qty),

                             Revenue = '$' + convert(VARCHAR,sum(t.price * s.qty),1)

     FROM     Authors a

              INNER JOIN TitleAuthor ta

                ON a.au_id = ta.au_id

              INNER JOIN Titles t

                ON t.title_id = ta.title_id

              INNER JOIN Sales s

                ON s.title_id = t.title_id

              INNER JOIN Stores st

                ON st.Stor_ID = s.stor_id

     WHERE    st.state = @State

     GROUP BY a.au_id,

              a.au_fname,

              a.au_lname

     ORDER BY QtySold DESC,

              LastName,

              FirstName)

GO

SELECT *

FROM   fnSalesByAuthorForState('CA')

UNION

SELECT *

FROM   fnSalesByAuthorForState('WA')

GO

/* Partial results

State SSN         FirstName   LastName    QtySold     Revenue

CA    213-46-8915 Marjorie    Green       35          $104.65

CA    267-41-2394 Michael     O'Leary     20          $299.80

CA    274-80-9391 Dean        Straight    15          $299.85

CA    427-17-2319 Ann         Dull        50          $1,000.00

*/

------------

Related link:

Table-Valued User-Defined Functions