|
sp_wiz
Yak Posting Veteran
55 Posts |
 Posted - 2001-06-25 : 07:50:54
|
| I found an article on 4guys that talked about using a sp to page through recordsets. I have implemented this solution on a site I'm currently developing. The initial testing does seem to prove that that this is a really neat solution.I'm interested in your thoughts on this type of approach, and any alternatives you may have.Below is the current SPCREATE PROCEDURE DIY_Paged_Search( @Page int, @RecsPerPage int, @category int, @subcat int, @make int)ASSET NOCOUNT ON--- Create a temp table ------CREATE TABLE #TempItems( [ID] [int] Identity, [Product_ID] [int] NOT NULL , [SKU] [varchar] (50) NULL , [ItemPic] [varchar] (50) NULL , [ItemName] [varchar] (100) NULL , [Make] [varchar] (50) NULL , [CategoryID] [int] NULL , [SubCatID] [int] NULL , [Description] [varchar] (500) NULL , [Units] [int] NULL , [Pack] [nvarchar] (50) NULL , [Our_Price] [money] NULL , [RR_Price] [money] NULL ) --- Insert th rows into the temp table ---INSERT INTO #TempItems ( [Product_ID], [SKU], [ItemPic], [ItemName], [Make], [CategoryID], [SubCatID], [Description], [Units], [Pack], [Our_Price], [RR_Price])SELECT [Product_ID], [SKU], [ItemPic], [ItemName], [Make], [CategoryID], [SubCatID], [Description], [Units], [Pack], [Our_Price], [RR_Price] FROM DIY_Products WHERE categoryid = @category and subcatid = @subcat--- Find the first and last record ---DECLARE @FirstRec int, @LastRec intSELECT @FirstRec = (@Page -1) * @RecsPerPageSELECT @LastRec = (@Page * @RecsPerPage+ 1) --- Return the set of paged records ----SELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec ) , TotalRecords = (SELECT COUNT(*) FROM #TempItems)FROM #TempItems WHERE ID > @FirstRec AND ID < @LastRecrobp |
|
Paged Recordsets