best way to produce "paging" effect in results

[andrewbadera]

hello-

I'm developing an online e-comm site with 5700 products; this of course leads to product searches returning dozens, if not hundreds or thousands, of results. As such, I need to page the results, 25 or 50 to a page. What's the best way to do that? The IDs in the database are sequential at the moment, but that's not guaranteed in the future. Is there something I could do with the SELECT clause similar to TOP like "BETWEEN" passing the next first/last/whatever values and order the results each time?

thanks in advance ...

 

[forecasting]

If you don't get any other ideas, you could create a temporary table using the SELECT INTO clause and insert a new IDENTITY. One of the advantages to this approach is that you can sort the results and still have paging.

SELECT *, IDENTITY(smallint, 100, 1) AS job_num
INTO UniqueNameNewTable
FROM (SELECT query to obtain what the customer wants)

 

[forecasting]

I forgot to mention that you need to delete the table when the user moves on or the session ends.

 

[cmmrfrds]

If you are using ASP and ADO then you should not need to worry about a sequence number. ADO has all the paging properties you need to do the paging in your web page. Look under ADODB Recordset for pagecount and pagesize properties. You can order the data by any column and put 25 or 50 on a page.

 

[tlbroadbent]

Check these resources for some other ideas.

http://www.15seconds.com/issue/010308.htm

http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp

http://www.aspfaqs.com/webtech/062899-1.shtml

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[andrewbadera]

thanks!

 

[essa2000]

Dear ;

All the above stuff is helpfull and it is an other example.


CREATE PROCEDURE stp_Paging
@Page int,
@Size int
AS

DECLARE @Start int, @End int

BEGIN TRANSACTION GetDataSet

SET @Start = (((@Page - 1) * @Size) + 1)

IF @@ERROR <> 0 GOTO ErrorHandler
SET @End = (@Start + @Size - 1)

IF @@ERROR <> 0 GOTO ErrorHandler

CREATE TABLE #TemporaryTable
(Row int IDENTITY(1,1) PRIMARY KEY,
OrderItemID Int
)

IF @@ERROR <> 0 GOTO ErrorHandler

INSERT INTO #TemporaryTable
SELECT OrderItemID from OE_ORD_OrderItems

IF @@ERROR <> 0 GOTO ErrorHandler

SELECT Row , OrderItemID
FROM #TemporaryTable
WHERE (Row >= @Start) AND (Row <= @End)

IF @@ERROR <> 0 GOTO ErrorHandler

DROP TABLE #TemporaryTable

COMMIT TRANSACTION GetDataSet

RETURN 0

ErrorHandler:

ROLLBACK TRANSACTION GetDataSet

RETURN @@ERROR


-- To execute it

Exec stp_Paging 1 , 20


Regards,
Essa