-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE p (
    id INTEGER NOT NULL,
    cat CHAR(1),
    txt VARCHAR(10),
    PRIMARY KEY(id)
)
GO
INSERT INTO p VALUES (1, 'A', 'Record 1')
GO
INSERT INTO p VALUES (2, 'A', 'Record 2')
GO
INSERT INTO p VALUES (3, 'A', 'Record 3')
GO
INSERT INTO p VALUES (4, 'B', 'Record 4')
GO
INSERT INTO p VALUES (5, 'A', 'Record 5')
GO
INSERT INTO p VALUES (6, 'A', 'Record 6')
GO
INSERT INTO p VALUES (7, 'A', 'Record 7')
GO
INSERT INTO p VALUES (8, 'A', 'Record 8')
GO
INSERT INTO p VALUES (9, 'B', 'Record 9')
GO
INSERT INTO p VALUES (10, 'A', 'Record 10')
GO
INSERT INTO p VALUES (11, 'A', 'Record 11')
GO
INSERT INTO p VALUES (12, 'A', 'Record 12')
GO
SELECT * FROM p
GO
CREATE PROCEDURE getpag @pagsiz INTEGER, @pagnum INTEGER, @cat CHAR(1)
AS
BEGIN
    DECLARE @nrows INTEGER
    DECLARE @npags INTEGER
    DECLARE @firstrow INTEGER
    DECLARE @lastrow INTEGER
    SELECT @nrows = COUNT(*) FROM p WHERE cat = @cat
    SET @npags = (@nrows + @pagsiz - 1) / @pagsiz
    SET @firstrow = (@pagnum - 1) * @pagsiz + 1
    SET @lastrow = @pagnum * @pagsiz
    ;
    WITH temp AS (SELECT *,ROW_NUMBER() OVER(ORDER BY id ASC) AS rn FROM p WHERE cat = @cat)
    SELECT @pagnum AS currpage,@npags AS totalpages,* FROM temp WHERE rn BETWEEN @firstrow AND @lastrow
END
GO
EXEC getpag 4,1,'A'
GO
EXEC getpag 4,2,'A'
GO
EXEC getpag 4,3,'A'
GO
EXEC getpag 5,2,'A'
GO
DROP PROCEDURE getpag
GO
DROP TABLE p
GO
