views:

120

answers:

3
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)?

+10  A: 

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

Espo
+7  A: 

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

select top (@top) * from tablename
Guffa
Thanks for your effort, it really helped to me!!!
Paresh
+2  A: 

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
Also note that SET ROWCOUNT doesn't affect the execution plan, so using TOP is preferred in SQL 2005.
Guffa
Thanks for alternative answer
Paresh