Can I create a SQL stored procedure that willl return a requested quantity of records and where the starting record may change with each call. Picture a website where users will see the first 25 results, click next and get the next 25, etc. and then click back and see the previous 25, etc. Also, they can change the number of results they see at once, so they might require results 1-40, then 41-80, etc.
The query will be complicated and there won't be a column to identify record numbers in any tables, meaning the query may build hundreds of records that will be unique with each call and no preexisting means to identify record numbers.
I picture a simple example to be something like this
CREATE PROCEDURE [BuildItems]
@nStartingRecord AS INT,
@nNumberOfRecordsToReturn AS INT,
@cKeyItemCategories AS UNIQUEIDENTIFIER
SELECT * FROM Items
WHERE Items.keyItemCategories = @cKeyItemCategories AND
RecordNumber >= @nStartRecord AND RecordNumber <= @nStartRecord+@nRecordCount