SQL Server - use a parameter to select the top X of the result set [duplicate]

Question

I am creating a SQL Server query that will take a parameter and use that as the record number to return.

In pseudo code:

parameter returnCount  select top returnCount * from table where x = y 

What is the correct syntax/code to perform that operation?

Answer 1

In SqlServer 2005 and up, do this:

CREATE PROCEDURE GetResults    (     @ResultCount   int ) AS  SELECT top(@ResultCount) FROM table where x = y 

For earlier versions, use:

CREATE PROCEDURE GetResults    (     @ResultCount   int ) AS  SET ROWCOUNT @ResultCount  SELECT * FROM table where x = y 

http://www.4guysfromrolla.com/webtech/070605-1.shtml for more information.

Answer 2

As of SQL Server 2005 (but not before that), you can define a variable to determine your number of TOP rows returned:

DECLARE @returnCount INT  SET @returnCount = 15  SELECT TOP (@returnCount) *  FROM dbo.table  WHERE x = y