upproductgetlist_listbypage_benchmark.sql

来自「基于微软的 ASP.NET+C#开发的PETSHOP(网上宠物店)项目,在性能及」· SQL 代码 · 共 57 行

/******************************************************************************
**        File: upProductGetList_ListByPage_Benchmark.sql
**        Name: upProductGetList_ListByPage Stored Procedure
**        Desc: Gets the list of products via the selected category. The 
**              results are returned in pages for better performance. This 
**              would allow the component using this stored procedure to 
**              return a DataReader and use custom paging with an ASP.NET 
**              WebForm DataGrid control. This was modified to return the TOP
**              50 results for the benchmarking.
**
**        Date: 11/6/2001
**
*******************************************************************************/

ALTER PROCEDURE upProductGetList_ListByPage
(
    @cat_id              char(10),
    @nCurrentPage        int,
    @nPageSize           int,
    @totalNumResults     int output
)
AS

-- we are creating a temporary table to store the currently
-- selected page of data. a rowid field has been added to allow
-- us to track which page we are on (the productid didn't work
-- in this case because it is a character data type and it is
-- much easier to calculate the paging with an int
CREATE TABLE #ProductCategoryTempTable
(
    rowid               int           IDENTITY PRIMARY KEY,
    productid           char(10)               NOT NULL,
    name                varchar(80)            NULL
)

-- copy the search results into the temporary table
INSERT INTO #ProductCategoryTempTable (productid, name)
SELECT TOP 50 productid, name
FROM Product
WHERE category = @cat_id
ORDER BY name

-- always return the total number of items found in the search
SELECT @totalNumResults = @@ROWCOUNT

-- calculate the current page
DECLARE @nFirstPageRecord int
DECLARE @nLastPageRecord int
SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1)

-- select the correct page of data with the given page size
SELECT productid, name
FROM #ProductCategoryTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)