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

Question

Using the vs2008 query builder, I’m trying to make a query that gets a parameter for the "TOP" Command, and then I face an error "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 

alt text http://www.freeimagehosting.net/uploads/f9b9354577.jpg

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).