What I found was that it took lots of searching till I found this page of various implementations.
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
Turns out the rowcount one works perfect for my need to access a 6.5 compatibility mode database. (no top functionality).
Here is how I implemented that in the stored procedure (Webservice implementation perhaps later):
CREATE PROCEDURE spimCounts_FetchRowsByGun_xxx (@gunid char(10), @iPageNum int = 1, @iPerPage int = 10)
AS
begin
set nocount on
DECLARE @ubound INT, @lbound INT, @pages INT, @rows INT
declare @iGunId char(10), @PageNum int, @PerPage int
declare @entrytime datetime
-- Reassign passed parameters to
-- help prevent parameter sniffing performance issues.
select @iGunID = @gunID
select @PageNum = @iPageNum, @PerPage = @iPerPage
-- Find the number of rows to work with and number of pages
SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
timItemCounts_csi WITH (NOLOCK)
where gunid = @gunid
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum @pages SET @pagenum = @pages
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT
CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows
SET ROWCOUNT @lbound
SELECT
@entrytime = entrytime
FROM
timItemCounts_csi WITH (NOLOCK)
where gunid = @gunid
ORDER BY
entrytime desc
SET ROWCOUNT @perPage
SELECT entryid, LocationID as 'LocID', ItemID, UPCID, Qty, EntryTime as 'Entry'
FROM timItemCounts_csi WITH (NOLOCK)
WHERE gunid = @gunid
and entrytime
end
What I found was that it took lots of searching till I found this page of various implementations.http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
Turns out the rowcount one works perfect for my need to access a 6.5 compatibility mode database. (no top functionality).
Here is how I implemented that in the stored procedure (Webservice implementation perhaps later):
CREATE PROCEDURE spimCounts_FetchRowsByGun_xxx (@gunid char(10), @iPageNum int = 1, @iPerPage int = 10)ASbegin set nocount on
DECLARE @ubound INT, @lbound INT, @pages INT, @rows INT declare @iGunId char(10), @PageNum int, @PerPage int declare @entrytime datetime
-- Reassign passed parameters to -- help prevent parameter sniffing performance issues. select @iGunID = @gunID select @PageNum = @iPageNum, @PerPage = @iPerPage
-- Find the number of rows to work with and number of pages SELECT @rows = COUNT(*), @pages = COUNT(*) / @perpage FROM timItemCounts_csi WITH (NOLOCK) where gunid = @gunid
IF @rows % @perpage != 0 SET @pages = @pages + 1 IF @pagenum @pages SET @pagenum = @pages SET @ubound = @perpage * @pagenum SET @lbound = @ubound - (@perpage - 1) SELECT CurrentPage = @pagenum, TotalPages = @pages, TotalRows = @rows
SET ROWCOUNT @lbound
SELECT @entrytime = entrytime FROM timItemCounts_csi WITH (NOLOCK) where gunid = @gunid ORDER BY entrytime desc SET ROWCOUNT @perPage SELECT entryid, LocationID as 'LocID', ItemID, UPCID, Qty, EntryTime as 'Entry' FROM timItemCounts_csi WITH (NOLOCK) WHERE gunid = @gunid and entrytime end
