Problem
In SQL Server, how can I have a dynamic variable that determines the number of rows to return? In SQL Server 2005+, the following syntax is not valid:
DECLARE @count int
SET @count = 20
SELECT TOP @count * FROM SomeTable
Asked by eddiegroves
Solution #1
SELECT TOP (@count) * FROM SomeTable
This will only work with SQL 2005 and later versions.
Answered by Brian Kim
Solution #2
Only SQL SERVER 2005+ supports the syntax “select top (@var)…”. You can perform the following with SQL 2000:
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
Oisin.
(thanks to augustlights for changing @@rowcount to rowcount)
Answered by x0n
Solution #3
In the case of x0n, it should be:
SET ROWCOUNT @top
SELECT * from sometable
SET ROWCOUNT 0
http://msdn.microsoft.com/en-us/library/ms188774.aspx
Answered by Codewerks
Solution #4
Alternatively, you may simply put the variable in parenthesis.
DECLARE @top INT = 10;
SELECT TOP (@Top) *
FROM <table_name>;
Answered by ShawnThompson
Solution #5
declare @rows int = 10
select top (@rows) *
from Employees
order by 1 desc -- optional to get the last records using the first column of the table
Answered by David Castro
Post is based on https://stackoverflow.com/questions/175962/dynamic-select-top-var-in-sql-server