CODEWITHSUNDEEP
sqlstored-proceduresparameters
Dani
Dani

Reputation: 15069

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

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

Upvotes: 0

Views: 1470

Answers (2)

manji
manji

Reputation: 47978

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.

Upvotes: 6

Andomar
Andomar

Reputation: 238126

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

Upvotes: 3

Related Questions