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.

15 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 UTC

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 UTC

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 UTC

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

Neel Bhatt

Sep 10 2007 at 10:50 UTC

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 UTC

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

Tom Lea

Nov 9 2007 at 15:28 UTC

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 UTC

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 UTC

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!