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

This question already has an answer 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

  • How to get a specific column of a table in a dataset?
  • Stock Control Determinant
  • Select Top 1 field and assign to local variable
  • SQL Foreign Key Relation
  • Restrict ObjectSet to a related Entity and sort by a dynamic column name
  • How to add multiple file extensions to the Files: input field in the Foreach loop container SSIS
  • 2 Solutions collect form web for “SQL Server – use a parameter to select the top X of the result set”

    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.

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.