03. April 2010 · Comments Off on Paging Data with a webservice · Categories: T-SQL · Tags: ,
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

Comments closed.