SQL Server FAQ - Creating Multi-Statement Table-Value Functions

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Create an Multi-Statement Table-Valued Function?

To create a multi-statement table-valued function, you need to define a temporary table as the returning table in the function. INSERT statements should be used to insert data into the returning table.

The tutorial exercise below shows you a simple example of how to build a temporary table and make it as the returning table of a function:

USE FyiCenterData;
GO

CREATE FUNCTION Yearly_Stats(
      @start_year INT, @end_year INT)
   RETURNS @stats TABLE (year INT, 
      min INT, max INT, counts INT)
   AS BEGIN
      DECLARE @year INT;
      SET @year = @start_year;
      WHILE @year <= @end_year BEGIN
         INSERT INTO @stats
            SELECT @year AS year, MIN(counts) AS min, 
               MAX(counts) AS max, COUNT(*) AS counts
            FROM fyi_links WHERE DATEPART(YEAR, created) = @year;
         SET @year = @year + 1;
         END;
      RETURN;
   END 
GO

SELECT * FROM dbo.Yearly_Stats(1900, 1905);
GO
year        min         max         counts
----------- ----------- ----------- -----------
1900        -999932     996991      638
1901        -997138     991874      592
1902        -996779     997315      594
1903        -995476     998520      636
1904        -994838     998956      635
1905        -993178     996249      595
(6 row(s) affected)