Microsoft SQL Server Common Table Expressions Recursive CTE
Example
This example shows how to get every year from this year to 2011 (2012 - 1).
WITH yearsAgo
(
myYear
)
AS
(
-- Base Case: This is where the recursion starts
SELECT DATEPART(year, GETDATE()) AS myYear
UNION ALL -- This MUST be UNION ALL (cannot be UNION)
-- Recursive Section: This is what we're doing with the recursive call
SELECT yearsAgo.myYear - 1
FROM yearsAgo
WHERE yearsAgo.myYear >= 2012
)
SELECT myYear FROM yearsAgo; -- A single SELECT, INSERT, UPDATE, or DELETE
| myYear |
|---|
| 2016 |
| 2015 |
| 2014 |
| 2013 |
| 2012 |
| 2011 |
You can control the recursion (think stack overflow in code) with MAXRECURSION as a query option that will limit the number of recursive calls.
WITH yearsAgo
(
myYear
)
AS
(
-- Base Case
SELECT DATEPART(year , GETDATE()) AS myYear
UNION ALL
-- Recursive Section
SELECT yearsAgo.myYear - 1
FROM yearsAgo
WHERE yearsAgo.myYear >= 2002
)
SELECT * FROM yearsAgo
OPTION (MAXRECURSION 10);
Msg 530, Level 16, State 1, Line 2The statement terminated. The maximum recursion 10 has been exhausted before statement completion.
