how to get top n rows from a table where value of n is passed at run-time?

How to get top n rows from a table wher开发者_开发问答e value of n is passed at run-time?

In SQL Server 2005 and beyond you can actually parameterise the top command.

The code below is 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 will need to either use rowcount or dynamic sql.

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 either of:

SET ROWCOUNT { number | @number_var }