TopN Stored Procedure

One of the websites that I work on has some learning based games and the web designer would like to display the top 5 or 10 games played. A nice idea. I concluded that the number of games played that could be displayed should not be limited to 5 or 10 but could be any number, e.g. 8, 7 or 83.

I started out using the TOP keyword as part of the SELECT statement:


The problem here is that T-SQL will not allow a variable to be used within a SELECT statement. The common solution to this is to use dynamic SQL which is not pretty to look at and causes a performance hit on the server. A better solution is to use a seeded temporary table.

First create a temporary table with a seeded ID (auto identity) column along with the columns required for the result. Then insert the results from the main query into the temporary table. Remember to set the sort order you require when inserting the rows. Finally, select the results from the temporary table where the ID is not greater than the number of results you need e.g. the top 10 games

Here is my working example:


A simple solution to a common problem. Temporary tables are a very useful tool for transforming the shape data and often remove the need for dynamic SQL or cursors.