Getting a range of values from an arbitrary starting point in T-SQL

Recently a friend of mine called with a question about how to do something in T-SQL that he wanted to do for the leaderboard for a game he wrote.  The issue was that tens of thousands of people are playing his game (on the iPhone)  and the screen real estate only allowed for about 10 players scores to be displayed.  The present method only displayed the top 10 scores, and did not include the current players score, so they never really saw where people were in relation to them.

He wanted something a bit more relevant to the player.  The solution was to display the closest 9 scores in relation to the player, 4 greater than, and 5 less than.  This is the solution I presented to him on a conceptual basis, actual implementation may vary and you would probably want to create an indexed view to run this query against with a clustered index on the column containing the players score and the players UserId, adding other columns as necessary to display the data you want.  This example will run as is without creating any tables or making any temp tables (uses only Table type variables).

declare @PlayerScores table (ScoreValue int)
declare @TempScores table(ScoreValue int)
declare @currentScore int 

set @currentScore = 0

while (@currentScore < 101)
      begin 
            set @currentScore = @currentScore + 1
            insert into @PlayerScores (
                  [ScoreValue]
            ) values ( 
                  /* ScoreValue - int */ @currentScore )  
      end 

set @currentScore = 50

insert into @TempScores (
      [ScoreValue]
) 
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] > @currentScore
 order by [ScoreValue] asc
insert into @TempScores (
      [ScoreValue]
)  
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] <= @currentScore
 order by [ScoreValue] desc


select * 
  from @TempScores
 order by [ScoreValue]