T-SQL – Using a parameter in SELECT TOP clause

I’ve been working with Stored Procedures a lot recently, even though the whole idea was new to be before starting this job. Today I was trying to select the first n rows from a table, but wanted to be able to change n via a parameter in the procedure call. I thought something like the following would work, but it throws an error when trying to use the variable for the TOP clause.

CREATE PROCEDURE dbo.sp_TestGetAll

    @LIMIT INT

AS

SELECT TOP @LIMIT

    id, col1, col2, col3

FROM TestTable

After a brief Google search there were a few methods presented including such nasties as manually writing out a SQL string within the procedure and executing that instead! Also, it was suggested to change the ROWCOUNT variable before and after issuing the select. Sheesh, that’s messy.

Well anyway, the solution which works for me and is much easier is simply to wrap the variable in parenthesis and magic… it works.

CREATE PROCEDURE dbo.sp_TestGetAll

    @LIMIT INT

AS

-- Notice the parentheses around @Limit - that is the only change!

SELECT TOP (@LIMIT) 

    id, col1, col2, col3

FROM TestTable

I’m just really noting this down so that I don’t forget, do another Google search and then resort to those other ugly ways of doing this simple task.

32 Comments on T-SQL – Using a parameter in SELECT TOP clause.

Add your two pennies, others have already donated theirs.

Sujith

Apr 9 2007 at 11:27 BST

I think this works only with SQL 2005.
I tried with SQL 2000, it is not working.

Thanx
Sujith

Drew Noakes

Jul 16 2007 at 13:10 BST

Thank you for this — saved me a lot of time. This page came up as the first link in Google for my search.

Barry Bartlett

Aug 2 2007 at 22:03 BST

Nice one. I shall use it with a Report I am writing.

Neel Bhatt

Sep 10 2007 at 10:50 BST

Hi,

Thanks a lot. To get around this problem, I was constructing an SQL query in the stored procedure and executing it.

Now this tip made it really simple.

Thanks & Regards,
Neel.

Michael Colp

Sep 21 2007 at 22:18 BST

Thanks, you just saved me what would have surely been a huge headache.

Tom Lea

Nov 9 2007 at 15:28 BST

For SQL 2000 try:

SET ROWCOUNT @PageSize;
SELECT a,b,c,d from e where f=’g’
SET ROWCOUNT 0

I’m sure this is available elsewhere, but this is the most promising google result!

Ofer Presente

Jan 13 2008 at 14:23 BST

The parameterized top does work (only in 2005 as was stated here), but it can be very slow sometimes. I wrote a query that uses

set @Limit=40
select top (@Limit) …..

This query takes 15 seconds. When the very same query is changed to
select top (40) ….

it takes 3 seconds – 1/5 of the duration of the parameterized top!!!

John

Apr 2 2008 at 08:08 BST

This had me really excited, until I tried it. I concur with what the last poster wrote. I used this trick to query a table in SQL Server 2005 and my query went from less than a second with a hardcoded value for TOP, to 26 seconds (!!) using the same value in a parameterized TOP. Boo!

Andell

Apr 27 2008 at 12:47 BST

SELECT Top (@Variable) … , actially works on SQL2005

CLINKS

Jan 20 2009 at 23:50 BST

Thanks! Save me a lot of time as well!

Vladimir

May 6 2009 at 09:40 BST

Its what i have had problems with as well. Quick & clear solution! Thanks!

Jul 16 2009 at 10:49 BST

Thanks for writing this had a series of if statements otherwise, ta

Dean

Jul 28 2009 at 20:36 BST

Thanks for the tip, it works great. I’m using SQL Server 2008 and I am not noticing any performance issues.

Petulka77

Jan 6 2010 at 01:15 BST

Thnks! That is what I am looking for!

BruceS

Feb 15 2010 at 05:24 BST

Many thanks. I have been looking around for this answer.

Dao T

Jul 28 2010 at 07:05 BST

Thanks alot, That great tip saved me lots of time !!!

michael

Oct 12 2010 at 19:43 BST

thanks for code, it works in sql 2005 express…

tamer

Oct 20 2010 at 18:01 BST

3.5 years after your post, and it’s still helpful, saved me from the nasty construction of a string and executing it….much cleaner here!!!

Gripper

Oct 22 2010 at 13:34 BST

Genius! That saved me hours of messing about trying to use EXEC etc….

Many thanks!

Roy

Nov 10 2010 at 01:29 BST

Tks, it’s great!

Afshar Mohebbi

Nov 26 2010 at 10:50 BST

Many thanks! helped me a lot!

Dan

Nov 29 2010 at 23:44 BST

How about selecting like this? I cant figure out the right formatting to make it work.

SELECT @TableName.@ColumnName
FROM @TableName

Michael

Dec 23 2010 at 21:18 BST

Thanks!

Praveen

Jan 15 2011 at 05:05 BST

Good one. This is the way we have to use.
http://praveenbattula.blogspot.com/2011/01/using-variables-in-top-clause-in-t-sql.html

Mar 4 2011 at 05:03 BST

Thanks ! First search result on google and very helpful.

CMH

May 4 2011 at 21:56 BST

Thank You! saved me from nasty dynamic SQL.

Anthony

Jul 19 2011 at 01:59 BST

Thanks. I was going to dynamic SQL.

Lazar Toronto

Aug 12 2011 at 17:39 BST

U r KING!! It helped a lot!!

Peter Mankge

Oct 3 2011 at 09:48 BST

Thanks very much, my last option was to break my long sql stament and try different approach, this helped me to save a lot of time, thanks once again, you are a star.