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.
So, our first try is to create a procedure that accepts @top as a parameter:
But, as we know, we can't do this in SQL Server:
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):
Or, they go back to the procedure route, and use dynamic SQL there:
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:
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:
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:
More specifically, continuing with the above example, we can achieve the desired results by:
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:
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.
In SQL Server 2005, we can still do this "the easy way":
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?