How do I use a variable in a TOP clause in SQL Server?

Sometimes developers want to allow the end user to determine how many rows are returned by a query. For example, say we have a table that contains dates, we might want to allow users to select a user-defined number of the most recent rows. 
 

CREATE TABLE blat(foo SMALLDATETIME)  SET NOCOUNT ON  INSERT blat SELECT '20040101'  INSERT blat SELECT '20040105'  INSERT blat SELECT '20040108'  INSERT blat SELECT '20040110'  INSERT blat SELECT '20040112'

 
So, our first try is to create a procedure that accepts @top as a parameter: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SELECT TOP @top foo          FROM blat          ORDER BY foo DESC  END  GO

 
But, as we know, we can't do this in SQL Server: 
 

Server: Msg 170, Level 15, State 1, Procedure getFoo, Line 5  Line 5: Incorrect syntax near '@top'.

 
Once this fails, many people's first inclination is to use dynamic SQL, either from the application or within T-SQL. For example, you could do this from ASP or VB (this code sample is in ASP): 
 

<%      ' ...      top = 3      sql = "SELECT TOP " & top & " foo FROM blat" & _          " ORDER BY foo DESC"      ' ...  %>

 
Or, they go back to the procedure route, and use dynamic SQL there: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      DECLARE @sql VARCHAR(255)      SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR, @top) + ' foo'          + ' FROM blat'          + ' ORDER BY foo DESC'      EXEC(@sql)  END  GO

 
Now, this will work, however you lose some performance both with the convert of the parameter (which, admittedly, you could pass in as varchar) and, more importantly, because the query plan can never be cached due to using dynamic SQL. In addition, please read this article to see why you should avoid dynamic SQL when possible. 
 
To get around using dynamic SQL, some people have decided to use a subquery to limit the rows. Assuming foo is unique, you could do this: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SELECT foo FROM blat b1          WHERE (              SELECT COUNT(foo)                  FROM blat b2                  WHERE b1.foo <= b2.foo          ) <= @top          ORDER BY foo DESC  END  GO

 
While this avoids the pitfalls of dynamic SQL, it produces sub-par performance (due to a double table scan, or double (clustered) index scan if the column in question is indexed). 
 
My preferred way to handle this is to use the ROWCOUNT setting, which *can* be parameterized: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SET ROWCOUNT @top        SELECT foo          FROM blat          ORDER BY foo DESC        -- never forget to set it back to 0!      SET ROWCOUNT 0  END  GO

 
This has a slightly higher I/O cost than the dynamic SQL route, but it beats the sub-query method performance-wise. The delta in performance is more noticeable when the column is not indexed. 
 
Note that this might not be an acceptable approach if a larger part of this same scope (batch, procedure, etc.) is expecting to use a custom ROWCOUNT setting. 
 

---

SQL Server 2005 
 
SQL Server 2005 actually allows us to parameterize the TOP clause, using a variable, expression or statement. So you can do things like: 
 

SELECT TOP (@foo) a FROM table ORDER BY a    SELECT TOP (SELECT COUNT(*) FROM somewhere else) a FROM table ORDER BY a    SELECT TOP (@foo + 5 * 4 / 2) a FROM table ORDER BY a

 
More specifically, continuing with the above example, we can achieve the desired results by: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SELECT TOP (@top) foo          FROM blat          ORDER BY foo DESC  END  GO

 

---

TOP @n PERCENT 
 
A slightly more complicated example would be to use a parameter for TOP n PERCENT. We don't have as many alternatives here. Obviously, the dynamic SQL option still works: 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      DECLARE @sql VARCHAR(255)      SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR, @top) + ' PERCENT foo'          + ' FROM blat'          + ' ORDER BY foo DESC'      EXEC(@sql)  END  GO

 
To use ROWCOUNT, it will be a little bit trickier... we can use an initial query to determine the number of rows, and from there determine how many rows = @top PERCENT. 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SET NOCOUNT ON      DECLARE @limit DECIMAL(12,2), @limitINT INT      SELECT @limit = 1.00 * COUNT(*) * @top / 100.00          FROM blat        SET @limitINT = CONVERT(INT, @limit)        IF (          CONVERT(DECIMAL(12,2), @limit)          != CONVERT(DECIMAL(12,2), @limitINT)      )          -- it didn't round nicely so we round up          SET @limit = CONVERT(INT, @limit) + 1        SET ROWCOUNT @limitINT      SELECT foo FROM blat ORDER BY foo DESC      SET ROWCOUNT 0  END  GO

 
In SQL Server 2005, we can still do this "the easy way": 
 

CREATE PROCEDURE dbo.getFoo      @top INT  AS  BEGIN      SELECT TOP (@top) PERCENT foo          FROM blat          ORDER BY foo DESC           END  GO