How to dynamically create SQL inside a stored procedure?

I am having problem with 'TOP @.pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @.pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords
(
@.pageSize int,
@.previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @.pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @.previousMaxId) order by ProductId
RETURN

Try aSET @.@.ROWCOUNT instead.

Terri|||So is there a way of dynamically forming a SQL statement inside a stored procedure and then executing it inside the same stored procedure?

I guess, from what you suggested, I should remove the 'TOP @.pageSize' clause from the query, and then use just before the query the following clause - 'SET @.@.ROWCOUNT @.pageSize'.|||The rowcount suggestion works beautifully. I can always get the next 'n' records for the next page.

With this stored procedure, one can always get a fixed number of records for all pages except the last page, while implementing custom paging in a datagrid. Most books will mention an example of custom paging in a datagrid, but will also say that each page might contain a different number of records, if there are missing values of the identity column. But here, even if identity column values are missing ( example: id's = 1,2, 5 , 9, 11 where id's = 3,4,6,7,8,10 are missing ) we can still get a constant number of records per page in custom paging.

I am assuming that the query in this stored procedure is not going to create any inefficiencies on SQL Server side. If you think it will, then please give your feedback.|||SET @.@.ROWCOUNT can have some unexpected side effects if you've got a number of batches in your proc because it restricts the rows for all batches not just the one you want. So just be careful of that.

You could use sp_execute run the dynamic top N (or even wait for Yukon), but unless you get into the problem areas I'd stick with ROWCOUNT.