Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Top @n

Modified: 2008/09/29 10:32 by vanthoog - Categorized as: SQL Server
In SQL Server 2000 you can limit the number of rows returned by a select-query by using the TOP-expression. So for example the following query returns the first 4 rows in the table sysdatabases in the master database:

select top 4 * from master.dbo.sysdatabases

Sometimes, for example in a stored procedure, it would be useful to make the "number of rows to be returned" dynamic. So you would like to do something like:

declare @maxrows int

set @maxrows = 5

select top (@maxrows) * from master.dbo.sysdatabases

Unfortunately, this does not work in SQL Server 2000. But it does work in SQL Server 2005. In SQL Server 2005 this example returns the first 5 rows in the table sysdatabases in the master database. There is one subtle difference between the dynamic and the static syntax: in the dynamic syntax you need to use brackets around the variable containing the "number of rows to be returned", whereas this is not necessary in the static syntax.

This also works in a stored procedure. So the following will work as well:

create procedure GetDatabases
	@maxrows int
as
	select top (@maxrows) * from master.dbo.sysdatabases
GO

exec GetDatabases 6

 © Evident Interactive BV