We help IT Professionals succeed at work.

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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
yes very possible in sql 2005+

you would use the row_number() Over  windowing function probably to give a sequence to your rows....


of course only you via external means can guarantee that a consistent set of results is presented (other users can
insert/delete rows from the set whilst its being viewed)

Maybe, you mean something like this:



CREATE TABLE Items (
      id INT IDENTITY(1,1),
      item VARCHAR(50)
)

INSERT INTO Items (item) VALUES ('a')
INSERT INTO Items (item) VALUES ('b')
INSERT INTO Items (item) VALUES ('c')
INSERT INTO Items (item) VALUES ('d')
INSERT INTO Items (item) VALUES ('e')
INSERT INTO Items (item) VALUES ('f')
INSERT INTO Items (item) VALUES ('g')
INSERT INTO Items (item) VALUES ('h')
INSERT INTO Items (item) VALUES ('i')
INSERT INTO Items (item) VALUES ('j')
INSERT INTO Items (item) VALUES ('k')
INSERT INTO Items (item) VALUES ('l')
INSERT INTO Items (item) VALUES ('m')

GO

CREATE PROCEDURE [BuildItems]
      @nStartingRecord AS INT,
      @nNumberOfRecordsToReturn AS INT
AS
BEGIN
      SELECT id
      FROM (SELECT id,
                         item,
                         ROW_NUMBER() OVER (ORDER BY item) as RecordNumber
                  FROM Items) Items
      WHERE RecordNumber >= @nStartingRecord
              AND RecordNumber <= @nStartingRecord + @nNumberOfRecordsToReturn - 1
END
GO

EXEC BuildItems 1, 10
EXEC BuildItems 5, 5

DROP TABLE Items
DROP PROCEDURE BuildItems
CERTIFIED EXPERT
Top Expert 2011
Commented:
select columnnamelist
    from (
SELECT *
       ,row_number() over (order by keyitemcategories) as rn
      FROM Items
      WHERE Items.keyItemCategories = @cKeyItemCategories)
 as x where x.rn between  @nStartRecord AND  @nStartRecord+@nRecordCount
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.