Select Last (first) rows of a table in Stored Procedure using parameter (SQL 2008)

Question

I'm trying to create a stored procedure that uses

SELECT TOP 20 * from tblRecords ....

I want the number of rows returned to be sent to the procedure as a parameter.

For some reason it says I have a syntax error near the parameter I use:

SELECT TOP @PARAM from tblRecords ....

Is there a straight way to do it or will I need to construct the Statement using String concatenations and execute it....

Answer 1

In the stored procedure, before you do the select, issue a:

SET ROWCOUNT @param

That will limit the number of returned rows to @param.

After the query, set it back to 0:

SET ROWCOUNT 0

Answer 2

try :

SELECT TOP (@PARAM) * from tblRecords ....

MSDN recommends to use always parentheses, it's supported without for backward compatibility:

...For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.