SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

SQL Server Table-Valued Function Example

A SQL Server table-valued function is a user-defined function which returns a table data type as output.
What makes SQL Server table-valued functions more useful in some situations than sql views or sql stored procedures is like every sql function, a t-sql table-valued function also accepts parameters.
Also TVF's (table valued functions) can be referenced in sql SELECT queries just like a SQL Server database table.
These two features plus creating complex t-sql programming logic in user defined functions make a table-valued function powerful than the SQL view and SQL stored procedure objects.

The most well-known use of SQL Table-Valued Function is sql string split function.
Perhaps sql developers can think of the above example as part of this t-sql tutorial.

The following t-sql table-valued function example can be executed on Microsoft SQL Server 2008 R2 sample database AdventureWorks.

create function udf_ParseDate (
 @date as datetime
) returns @dateinfo table (
 id int identity(1,1),
 [date] datetime,
 [year] int,
 [month] smallint,
 [day] smallint
 insert into @dateinfo
 select @date, YEAR(@date), MONTH(@date), DAY(@date)


In fact, the above date parser function only returns a single row.
You might prefer to extract Year, Month, and Day date parts within SELECT statements without using a table-valued function.
But please think of udf_ParseDate table-valued function as a starting point in this t-sql tutorial.

 o.SalesOrderID, o.OrderDate,
 d.[year], d.[month], d.[day]
from Sales.SalesOrderHeader o
cross apply dbo.udf_ParseDate(o.OrderDate) d

The output of the above sql TVF is as follows.


In this t-sql tutorial, I want to give a derived date table example too.
The following t-sql table valued function can be used by t-sql programmers in order to create a table where the start and end date ranges are given as a parameter.

create function udf_CreateDateTable (
 @mindate as date,
 @maxdate as date
) returns @datetable table (
 id int identity(1,1),
 [date] date

 declare @i int = 0, @j int = DATEDIFF(dd, @mindate, @maxdate)
 while @i <= @j
  insert into @datetable
  select dateadd(d, @i, @mindate)

  set @i = @i + 1


T-SQL programmers can use the above user defined table-valued function to find the missing date gaps in SalesOrderHeader table according to the OrderDate column.
Again for this table-valued function example (TVF), I want to use AdventureWorks table.

with cte as (
  [MIN] = MIN(o.OrderDate),
  [MAX] = MAX(o.OrderDate)
 from Sales.SalesOrderHeader o
select d.id, d.date
from cte
cross apply dbo.udf_CreateDateTable(cte.MIN, cte.MAX) d
where not exists (
 select * from Sales.SalesOrderHeader where OrderDate = d.date

The following t-sql query which is using TVF - Table Valued Function udf_CreateDateTable will display the dates where no sales order has been created.
Here is the 3 days where no order was created in our AdventureWorks sample database table SalesOrderHeader.


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.