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

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?

two year old question, and you SO storm troopers are just now marking it as a duplicate?!! — pithhelmet, Jan 13 '14 at 16:12
Yeah well google prefers this question to the others - top result in search for me. So it was helpful, relevant, and +1 to you sirs. — Spacemonkey, Feb 11 '19 at 18:32

score 84 · Accepted Answer · answered Jun 08 '11 at 21:22

84

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

answered Jun 08 '11 at 21:22

agent-j

score 25 · Answer 2 · answered Jun 08 '11 at 21:20

25

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

answered Jun 08 '11 at 21:20

marc_s

2 Answers2