How to get the top n rows from a table where the value n is passed at runtime?

Question

How to get the top n rows from a table where the value n is passed at runtime?

Answer 1

In SQL Server 2005 and above, you can actually parameterize the top command.

Below is the code from MSDN

USE AdventureWorks; GO DECLARE @p AS int; SELECT @p=10 SELECT TOP(@p)* FROM HumanResources.Employee; GO 

In earlier versions of SQL Server, you need to either use rowcount or dynamic sql.

Answer 2

You can use set rowcount . To get the first 100, for example:

 declare @myrowcount = 100 set rowcount @myrowcount select ..... from ... where...order by 

since you can use any of them:

SET ROWCOUNT {number | @number_var}