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

This question already has answers here: Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate] (3 answers) SQL: How do I use parameter for TOP like in SELECT TOP @amount? [duplicate] (2 answers) Closed 9 years ago.

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 开发者_C百科syntax/code to perform that operation?

---

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

https://web.archive.org/web/20210417081325/http://www.4guysfromrolla.com/webtech/070605-1.shtml for more information.

---

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