Problem
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)?
Asked by Paresh
Solution #1
Yes, in SQL Server 2005 it’s possible to use a variable in the top clause.
select top (@top) * from tablename
Answered by Guffa
Solution #2
The TOP clause in SQL Server 2005 can be parameterized with a variable, expression, or statement. As a result, 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
Answered by Espo
Solution #3
You may do it in 2005 and later, as there are multiple replies in this topic.
What’s less well known is that you can do the same thing in 2k by using the SET ROWCOUNT command.
-- 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 that if you fail to add the SET ROWCOUNT 0 at the end, the restriction will remain… and you’ll end up with bugs that are difficult to find:-)
Answered by Brimstedt
Post is based on https://stackoverflow.com/questions/1927450/use-variable-with-top-in-select-statement-in-sql-server-without-making-it-dynami