Dynamic SELECT TOP @var In SQL Server

Question

How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:

DECLARE @count int
SET @count = 20

SELECT TOP @count * FROM SomeTable

Answer 1

SELECT TOP (@count) * FROM SomeTable

This will only work with SQL 2005+

Answer 2

The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:

set rowcount @top

select * from sometable

set rowcount 0

Hope this helps

Oisin.

(edited to replace @@rowcount with rowcount - thanks augustlights)

Answer 3

In x0n's example, it should be:

SET ROWCOUNT @top

SELECT * from sometable

SET ROWCOUNT 0

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Answer 4

Its also possible to use dynamic SQL and execute it with the exec command:

declare @sql  nvarchar(200), @count int
set @count = 10
set @sql = N'select top ' + cast(@count as nvarchar(4)) + ' * from table'
exec (@sql)