Here is the table function I created, which I think should only work in SQL Server 2005 and up because of the parametrized TOP value:
SET ANSI_NULLS ONSimple! Now you can call it like so:
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.udtf_GetDynamicYearList
(
@StartYear INT -- The first year you want in the list
, @NumberofYears INT -- The total number of years you want (from the Start Year)
)
RETURNS TABLE
AS
RETURN
(
SELECT
TOP (@NumberofYears) *
FROM
(
SELECT
@StartYear + n1.num AS [Year]
FROM
(
SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
) n1
) GenCalendar
ORDER BY 1
)
GO
SELECT * FROM dbo.udtf_GetDynamicYearList(2006, 10)If you want it to be more dynamic (say, to start from last year), you can do:
SELECT * FROM dbo.udtf_GetDynamicYearList(YEAR(GETDATE())-1, 10)
No comments:
Post a Comment