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.

Previous
Next

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

Add your two pennies, others have already donated theirs.

  1. Sujith
    Sujith
    Apr 9 2007 at 11:27 UTC

    Hi

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

    Thanx
    Sujith

  2. 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.

  3. Barry Bartlett
    Barry Bartlett
    Aug 2 2007 at 22:03 UTC

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

  4. 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.

  5. Michael Colp
    Sep 21 2007 at 22:18 UTC

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

  6. 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!

  7. Ofer Presente
    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!!!

  8. John
    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!

  9. Andell
    Andell
    Apr 27 2008 at 12:47 UTC

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

  10. CLINKS
    CLINKS
    Jan 20 2009 at 23:50 UTC

    Thanks! Save me a lot of time as well!

  11. Vladimir
    Vladimir
    May 6 2009 at 09:40 UTC

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

  12. EK
    EK
    Jul 16 2009 at 10:49 UTC

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

  13. Dean
    Dean
    Jul 28 2009 at 20:36 UTC

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

  14. Petulka77
    Petulka77
    Jan 6 2010 at 01:15 UTC

    Thnks! That is what I am looking for!

  15. BruceS
    BruceS
    Feb 15 2010 at 05:24 UTC

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

Post Comment