help me with Paging problem sql server 2000

hello,
I need paging of records using stored procedure, so that i dont need to
fetch all the records in a dataset just to display a few (say 50 or 100)
in a datagrid web control (asp.net 1.1).
I am new to writing sql scripts but i 'ld come up with a solution using
ROWCOUNT. ( thanks to links given in newsgroups)
However, all the solutions provided 'ld give the next or previous pages.
I am providing page numbers in aspx page (not using any paging
mechanism of datagrid,rather depending only on sql server to give paged
records). I pass the last ID (The primary key, also Identity column)
from the datagrid page thats currently displayed, the Page difference (
current pageindex - requestedpage index) and the numof records per page
as parameters to the stored proc.
heres the stored proc..
CREATE PROCEDURE [dbo].[vitPageWiseProducts]
( @.PageDifference [int],
@.RecordsPerPage [int],
@.LowerID [int],
@.Direction [int],
)
AS
if @.PpageDifference <= 0 set @.PageDifference = 1
if @.RecordsPerPage = 0 set @.RecordsPerPage = 20
if @.LowerID = 0 set @.LowerID = 99
if @.Direction <=0 set @.Direction = 0
set NOCOUNT on
declare @.pages int,@.recs int,@.totrecs int
declare @.ttt table(
[ProductID] [int],
[ProductName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductDescription] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int],
[QOH] [int] NOT NULL ,
[ProductUnitPrice] [smallmoney] NOT NULL ,
[ProductImageURL] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductInsertDate] [smalldatetime] NULL ,
[SourceCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationCountry] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
)
declare @.restable table(
[ProductID] [int],
[ProductName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductDescription] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int],
[QOH] [int] NOT NULL ,
[ProductUnitPrice] [smallmoney] NOT NULL ,
[ProductImageURL] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductInsertDate] [smalldatetime] NULL ,
[SourceCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationCountry] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
)
set @.pages=@.PageDifference
set @.recs=@.RecordsPerPage
set @.totrecs = @.recs*@.pages
-- the count(*) is taking time when num of records r morethan 100,000
-- select @.@.NumOfPages=CEILING(count(*)/@.recs) from pp
-- if @.Direction = 0 means
if @.Direction = 0
begin
set ROWCOUNT @.totrecs
insert into @.ttt select * from pp where ProductID > @.LowerID --order by
ProductID DESC
set ROWCOUNT @.recs
insert into @.restable select * from @.ttt order by ProductID DESC
set ROWCOUNT 0
--set nocount on
select * from @.restable order by ProductID ASC
--set nocount off
end
if @.Direction >= 1
begin
set ROWCOUNT @.totrecs
insert into @.ttt select * from pp where ProductID < @.LowerID order by
ProductID DESC
set ROWCOUNT @.recs
--insert into @.restable select * from @.ttt order by ProductID ASC
--set ROWCOUNT 0
--set nocount on
select * from @.ttt order by ProductID ASC
end
set nocount off
GO
The table structure is the same as the declared table variable...
i dont know if this is the optimum way to solve the paging problem.
what r the divantages of the above stored proc.
please help me find a better solution as i am new to stored procs...
I do not know what does cumulative client processing time, and
cumulative wait time on server replies mean 'Can you post some sample data & expected results for other to test? In the
meantime, refer to: www.aspfaq.com/2120 for some ideas.
Anith|||Anith Sen wrote:
> Can you post some sample data & expected results for other to test? In the
> meantime, refer to: www.aspfaq.com/2120 for some ideas.
>
Thanks Anith ,
I 'ld come up with the stored procedure only after reading th elink
given by u in the post.
Horstmanns RowCount method. I wanted a where clause in my query to make
it efficient and fetch only few records when comapred to a large set (in
my case 180000).
I will post the sample data and aspx page in short time ..
Thanks again
NS