Collections:
Creating Multi-Statement Table-Value Functions in SQL Server
How To Create an Multi-Statement Table-Valued Function in SQL Server Transact-SQL?
✍: FYIcenter.com
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)
⇒ Using Cursors in SQL Server Transact-SQL
⇐ "RETURNS TABLE" - Creating Inline Table-Value Functions in SQL Server
2016-10-17, 4332🔥, 0💬
Popular Posts:
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...
Collections: Interview Questions MySQL Tutorials MySQL Functions Oracle Tutorials SQL Server Tutoria...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...