Paging through results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Paging through results

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-10 : 12:41:40

Hi,

Now I am paging through the results of my ASP.NET application with this SP:


USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalRecords int output
As

CREATE TABLE #tempTable
(
 Id int IDENTITY PRIMARY KEY,
 Offer_id bigint,
 Date datetime,
 Offer_Title varchar(100),
 Company_name varchar(100),
 City_name varchar(100)   
)

INSERT INTO #tempTable
(
Offer_id,
 Date,
 Offer_Title,
 Company_name,
 City_name 
)

Select Offer_id, Date, Offer_Title, user.Company_name, city.City_name
From Offers As offe

JOIN Users As user
On offe.User_num = user.User_id
JOIN Cities As city
On offe.city_num = city.City_id

Order by Date DESC


Declare @FirstReg int, @LastReg int
Select @FirstReg = (@CurrentPage - 1) * @PageSize  
Select @LastReg = (@CurrentPage * @PageSize) + 1


SELECT Offer_id, Date, Offer_Title, Company_name, City_name  
FROM #tempTable
WHERE
Id > @FirstReg AND Id < @LastReg

SELECT @TotalRecords = COUNT(*) FROM #tempTable  
DROP TABLE #tempTable    
GO

And to avoid performance problems due to many users at the same time creating and dropping temp tables with many registers every time, I want to achieve the same results (performance) that now my SP produces but without using a temporary table. I found a code sample that I think does what I am looking for. The problem is that I don’ t know how to adapt my SP to this other. The technique is called ROWCOUNT.

Here is the sample I found in this page: http://www.aspfaq.com/show.asp?id=2120


CREATE PROCEDURE SampleCDs_Paging_Rowcount 
    @pagenum INT = 1, 
    @perpage INT = 50 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE 
        @ubound INT, 
        @lbound INT, 
        @pages INT, 
        @rows INT 
 
    SELECT 
        @rows = COUNT(*), 
        @pages = COUNT(*) / @perpage 
    FROM 
        SampleCDs WITH (NOLOCK) 
 
    IF @rows % @perpage != 0 SET @pages = @pages + 1 
    IF @pagenum < 1 SET @pagenum = 1 
    IF @pagenum > @pages SET @pagenum = @pages 
 
    SET @ubound = @perpage * @pagenum  
    SET @lbound = @ubound - (@perpage - 1)  
 
    SELECT 
        CurrentPage = @pagenum, 
        TotalPages = @pages, 
        TotalRows = @rows 
 
    -- this method determines the string values 
    -- for the first desired row, then sets the 
    -- rowcount to get it, plus the next n rows 
 
    DECLARE @aname VARCHAR(64), @title VARCHAR(64) 
 
    SET ROWCOUNT @lbound 
 
    SELECT 
        @aname = ArtistName, 
        @title = Title 
    FROM 
        SampleCDs WITH (NOLOCK) 
    ORDER BY 
        ArtistName, 
        Title 
 
    SET ROWCOUNT @perPage 
 
    SELECT 
        ArtistName, 
        Title 
    FROM 
        SampleCDs WITH (NOLOCK) 
    WHERE 
        ArtistName + '~' + Title 
        >= @aname + '~' + @title 
    ORDER BY 
        ArtistName, 
        Title 
 
    SET ROWCOUNT 0 
END 
GO

Does anyone know how can I adapt my initial SP to this technique? And, what do you think about it?

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 17:16:50

you won't have a problem with users and temp tables. how frequent are the db trips?
but check this out it's preety simple to implement:
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

Go with the flow & have fun! Else fight the flow

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-11 : 05:45:46

I tried this:


USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalRegisters int output
As

BEGIN 
    SET NOCOUNT ON 
 
    DECLARE 
        @ubound INT, 
        @lbound INT 
 
    SET @ubound = @PageSize * @CurrentPage  
    SET @lbound = @ubound - (@PageSize - 1)  

    Declare @Offer_id As bigint

    SET ROWCOUNT @lbound 
    Select @Offer_id = Offer_id
    From Offers
 
    SET ROWCOUNT @PageSize
    Select Offer_id, Date, Offer_title, user.Company_name, city.City_name
    From Offers As offe
    JOIN Users As user
    On offe.User_num = user.User_id
    JOIN Cities As city
    On offe.City_num = city.City_id
    Where Offer_id >= @Offer_id
   
    SELECT @TotalRegisters = COUNT(*) FROM Offers 
    SET ROWCOUNT 0 
    
END 
GO

And the result is: When I press next or previous button I always see the same offers. What is wrong?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-11 : 06:03:13

well it would be usefull if you put order by in there.
are you incrementing the page number correctly?

Go with the flow & have fun! Else fight the flow

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-11 : 07:01:15

If I put 'order by Date Desc' it does the same but ordered by date, I mean I always see the same offers when paging (the last ones inserted, from 1 to 10):


...
SET ROWCOUNT @PageSize
Select Offer_id, Date, Offer_title, user.Company_name, city.City_name
From Offers As offe
JOIN Users As user 
On offe.User_num = user.User_id    
JOIN Cities As city 
On offe.City_num = city.City_id    
Where Offer_id >= @Offer_id       
Order by Date Desc

SELECT @TotalRegisters = COUNT(*) FROM Offers
SET ROWCOUNT 0
END
GO

And I am incrementing the page number correctly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources