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

Question

I'm trying to do the following in a proc, but getting an incorrect syntax error:

SELECT TOP @NumberOfResultsToReturn *

What am I doing wrong here? Thanks.

Answer 1

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

Answer 2

Add parenthesis:

SELECT TOP (@NumberOfResultsToReturn) *

Answer 3

SQL Server: Put the argument in parens:

SELECT TOP (@NumberOfResultsToReturn) *

Answer 4

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.

Answer 5

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, !!!

Answer 6

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: