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, 2211🔥, 0💬
Popular Posts:
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...