Try this:
select top @sp_NumberRows from . . .
where @sp_NumberRows is a passed parameter to the Stored Procedure.
>Hi all...
>
>I'm trying to capture a subset of data, where I'm going to create an ADO (but it could be anything) representing records x through y of a given set. IOW, my total query returns, say, 3000 records, but my user only wants to see 50 at a time via a browser, with a "page x of y" navbar letting them jump to any page. I'd rather not have to create and scan an ADO with 3000 records in it just to get the 50 I'm looking for (the way they're doing it now). Enough users with enough big queries will generate enough ADOs with memory appetites to choke my server.
>
>With Oracle, something like this works:
>
>SELECT * FROM MyTable WHERE ThisIsTrue HAVING RowNum >= x and RowNum <= y
>
>I can't find anything similar in SQL Server (I'm running 7.0), and I'm not sure what path to try next. Any thoughts much appreciated... :)
>
>Scott