MS SQL Server Interview Question:

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

Submitted by: Administrator

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 GlobalGuideLineDatabase;
GO
<pre>
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
</pre>
Submitted by: Administrator

<pre>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)
</pre>
Submitted by: Administrator