How to do SELECT TOP @Param in a Stored Procedure?

Add parenthesis:

SELECT TOP (@NumberOfResultsToReturn) *

SQL Server: Put the argument in parens:

SELECT TOP (@NumberOfResultsToReturn) *

Here's how I was doing it in the old times:

SET @@ROWCOUNT = @NumberOfResultsToReturn
SELECT ........
SET @@ROWCOUNT = 0

This will work, although SELECT TOP (@NumberOfResultsToReturn) is preferable if you're using SQL server that supports this syntax:

This is supported in SQL Server 2005 and later, but not in SQL Server 2000. What version are you using?

You may have to use the RowNumber() method instead.

Here is an example:

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

EDIT Or you could use parentheses...which I was unaware of at the time :) Thanks guys.

I'm afraid you can't do this in SQL 2000, but you can try to construct the query

DECLARE @query VARCHAR(500)
set @query = 'SELECT TOP ' + @NumberOfResultsToReturn + '* FROM table'
EXEC @query

I didn't know the parenthesis trick for SQL 2005, thanks too guys, !!!