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
Yes, in SQL Server 2005 it’s possible to use a variable in the top clause.
select top (@top) * from tablename
Answered by Guffa
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
Answered by Espo
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