SQL SORT BY ALTERNATE YEAR

Question

Existing Data:

RowId   Title   Year
1        Aaa    2002
2        Bbb    2003
3        Ccc    2004
4        Ddd    2004
5        Eee    2005
6        Fff    2006
7        Ggg    2007
8        Hhh    2008

I have a need to retrieve the data from the above table based on the input parameter, year as follows:

    1.  Select all records matching param year
    2.  Select all records matching param year +1
    3.  Select all records matching param year -1
    4.  Select all records matching param year +2
    5.  Select all records matching param year -2
    6.  Select all records matching param year +3
    7.  Select all records matching param year -3

Expected Results (assuming param year is 2005):

RowId   Title   Year
5        Eee    2005
6        Fff    2006
3        Ccc    2004
4        Ddd    2004
7        Ggg    2007
2        Bbb    2003
8        Hhh    2008
1        Aaa    2002

I was able to achieve it using multiple SELECT on same table with UNION but wondering if there is a better way to do it.

Appreciate your help!

Answer 1

Assuming Year is an integer:

declare @param int

select *
from mytable
where Year = @param
or Year = @param + 1
or Year = @param - 1
-- etc.
order by abs(Year - @param), Year - @param desc, Title

The first clause in the order by sorts by the magnitude of the difference between Year and @param, and the second makes sure positive differences come before negative ones.