upitemgetlist_listbypage_benchmark.sql

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

/******************************************************************************
**        File: upItemGetList_ListByPage_Benchmark.sql
**        Name: upItemGetList_ListByPage Stored Procedure
**        Desc: Gets the list of items for a specific product type. The 
**              data is returned in pages for better performance.
**              This was modified to return the TOP 25 results for the 
**              benchmarking.
**
**        Date: 11/6/2001
**
*******************************************************************************/

ALTER PROCEDURE upItemGetList_ListByPage
(
    @prodid              varchar(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 itemid 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 #SearchResultsTempTable
(
    rowid               int           IDENTITY PRIMARY KEY,
    itemid              char(10)               NOT NULL,   
    listprice           decimal(10, 2)         NULL, 
    attr1               varchar(80)            NULL,
)

-- copy the search results into the temporary table
INSERT INTO #SearchResultsTempTable (itemid, listprice, attr1)
SELECT TOP 25 itemid, listprice, attr1
FROM   Item
WHERE productid = @prodid 

-- 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 itemid, listprice, attr1
FROM #SearchResultsTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)