243
declare @top  int
set @top = 5
select top @top * from tablename

Is it possible?

Or any idea for such a logic (i don't want to use dynamic query)?

Konamiman
  • 49,681
  • 17
  • 108
  • 138
Paresh
  • 3,659
  • 6
  • 27
  • 32

3 Answers3

453

Yes, in SQL Server 2005 it's possible to use a variable in the top clause.

select top (@top) * from tablename
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • it's completely weird...I wasn't aware of this and I got stuck for a few minutes on this. Thank you (+1) – Christos Feb 17 '16 at 10:37
  • 11
    What makes it even more weird ... the brackets are essential! Without the brackets around "@top" you'll get an incorrect syntax. Thanks! – Satria Jul 23 '18 at 13:15
  • Any idea why query execution takes considerably longer time when using top with a variable than it does when it is hardcoded? – Art Gertner May 11 '22 at 09:40
50

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

Source

Community
  • 1
  • 1
Espo
  • 41,399
  • 21
  • 132
  • 159
26

In 2005 and later, you can do it as there are several replies in this thread.

Whats less known is that you can achieve this also in 2k, by using SET ROWCOUNT.

  -- Works in all versions
  SELECT TOP 10

  -- Does not work on 2000
  SELECT TOP (10)
  SELECT TOP (@rows)

  -- Works in both 2ooo and 2oo5
  SET ROWCOUNT @max

  SELECT * 
  FROM ...

  SET ROWCOUNT 0

Note, if you forget the SET ROWCOUNT 0 at the end, the limit persists.. and you will end up with very hard to locate bugs :-)

Brimstedt
  • 3,020
  • 22
  • 32
  • 5
    Also note that SET ROWCOUNT doesn't affect the execution plan, so using TOP is preferred in SQL 2005. – Guffa Dec 18 '09 at 11:19
  • 2
    +1 for discussion about `SET ROWCOUNT`. This isn't the solution I used, but I like that you brought it up – Sam Sep 12 '13 at 00:39
  • 'o' is not the same as '0' – siride Jul 18 '14 at 04:12
  • 1
    Great solution, you got a +1 for this! I needed it for a stored procedure, in which I had to return data by using SELECT statement, which should be configurable. But the SP should also be able to return all rows. By using `if (@a>0) set rowcount @a else set rowcount 0` I was able to solve it without having to duplicate the SELECT statement ("traditionally" you need to have an if statement and the same SELECT exists twice - with and without TOP clause). – Matt Nov 25 '14 at 13:24
  • personal opinion, much better answer than parameterizing top (which I was gonna do). If I didn't want to set a parameter on top, I wouldn't have gotten all the records and would have kept it at 0, otherwise I would have had to include an if statement with two queries. As matt previously stated. – sksallaj Jun 12 '15 at 06:29
  • One issue with setting ROWCOUNT is that if you have subqueries it affects those as well. Example: SET ROWCOUNT 100; SELECT * FROM MyTable WHERE ID IN (SELECT Id FROM MyOtherTable WHERE value IS null); In the above example, ROWCOUNT also affects (SELECT Id FROM MyOtherTable WHERE value IS null) so only 100 rows are returned to be searched. In this example it doesn't really matter (very simple query), but in some cases it will. – Chris Woodruff Aug 30 '23 at 20:31