SQL: How do I use parameter for TOP like in SELECT TOP @amount?

Question

In the vs2008 query builder, I’m trying to make a query that gets a parameter for the "TOP" Command, but it won't let me, and it say's "Error in top expression"

Works:

SELECT TOP 5 * FROM dbo.SomeTable
WHERE SomeColumn = SomeValue

Doesn't Work:

SELECT TOP @param1 * FROM dbo.SomeTable
WHERE SomeColumn = SomeValue

Answer 1

Need parenthesis, and only for SQL Server 2005 and above

SELECT TOP (@param1) ...

Answer 2

For older versions of SQL Server, you can use:

SET ROWCOUNT @NumberOfResults
SELECT * FROM MyTable
SET ROWCOUNT 0

However, you should not use this technique on 2008:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server (2008). Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).