Link to home
Create AccountLog in
Avatar of woodwyn
woodwynFlag for United States of America

asked on

SQL stored procedure must return varying record counts starting at varying record numbers

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
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That would probably work out OK, as long as the total number of records doesn't get too high.

However, I would at least consider saving previous control values and doing a read-forward for the number of rows you need (plus one) from those values as opposed to re-reading all rows every time.