SQL List of year from this year to 5 years back, then used as parameter in stored sproc.

Question

First I need to create a sproc that will give me just a list of years from this year back to 5 years before this year. I was thinking of somehow using a table variable?

After that I need to use that value as a parameter in another sproc. How would I then say show all Dates that contain the year that is picked? I know how to write the parameter and everything I just need the Where clause part of it.

The date in the column are like this 2010/01/30 00:00.000

Answer 1

Part One:

Declare @Years Table (int year primary Key Not Null)
Declare @Yr integer Set @Yr = Year(getDate())
Declare @tYr Integer Set @tYr = @Yr
while @Yr > @TYr - 5 Begin
   Insert @Years(year) Values (@Yr)
   Set @Yr = @Yr - 1
End

Part TWO: (I assume you mean "Show all Dates That contain a year in the five year span" ??)

Where Year(dateColumn) In (Select year from @Years)

-- But this is not SARGable, so I would reccomend instead,

Where dateColumn Between DateAdd(year, Year(getDate())-1905, 0) 
                     And DateAdd(year, Year(getDate())-1899, 0) 

Answer 2

As an option you could use a temp table to store whatever years you need then drop it when your finished with it.

This example below will create a temp table with the following values. (2009, 2008, 2007, 2006, 2005)

-- Create Temp table
CREATE TABLE #years 
(
    [year]  int,
)

DECLARE @Max AS INT
DECLARE @Count AS INT

SET @Max = 5
SET @Count = 1

WHILE @Count <= @Max
BEGIN 

    INSERT INTO #years ([year])
    VALUES (YEAR(GETDATE())- @Count)
    SET @Count = @Count + 1

END

SELECT * FROM #years
--DR0P TABLE #years