Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

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)

  1. What Are User Defined Functions?
  2. What Are the Differences between User Defined Functions and Stored Procedures?
  3. How To Create a Simple User Defined Function?
  4. How To Use User Defined Functions in Expressions?
  5. How To List All User Defined Functions in the Current Database?
  6. How To Drop an Existing User Defined Function?
  7. How To Generate CREATE FUNCTION Script on an Existing Function?
  8. How To Get the Definition of a User Defined Function Back?
  9. How To Modify an Existing User Defined Function?
  10. How To Create User Defined Functions with Parameters?
  11. How To Provide Values to User Defined Function Parameters?
  12. Can You Pass Expressions to Function Parameters?
  13. How To Provide Default Values to Function Parameters?
  14. How Many Categories of Functions based Their Return Modes?
  15. How Many Ways to Create Table-Valued Functions?
  16. How To Create an Inline Table-Valued Function?
  17. How To Create an Multi-Statement Table-Valued Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...