SQL Server Solutions - Returning A Variable Number Of Rows In SQL Server (7.0/2000)


We all know how useful the TOP clause is for returning only the top n rows from a query. For example, to return just the top 3 employees in the Northwind database, you could use:


  • -- This works (of course)
  • SELECT TOP 3 *
  • FROM Employees

But what if the number of rows you want to return needs to be dynamic, varying based on some other conditions. You might try something like this, without success:


  • -- This does *not* work
  • DECLARE @Rows int
  • SET @Rows = 3
  •  
  • SELECT TOP @Rows *
  • FROM Employees

So, how can you address this challenge? The answer is to remember the ANSI SQL standard SET ROWCOUNT statement, for which the TOP clause is a Microsoft-specific alternative. The following code accomplishes the desired results:


  • -- But this *does* work
  • DECLARE @Rows int
  • SET @Rows = 3
  •  
  • SET ROWCOUNT @Rows
  • SELECT *
  • FROM Employees
  • SET ROWCOUNT 0

Go back