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, 2769🔥, 0💬
Popular Posts:
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...