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, 2205🔥, 0💬
Popular Posts:
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...