upitemgetlist_listbypage_benchmark.sql

基于微软的 ASP.NET+C#开发的PETSHOP(网上宠物店)项目,在性能及开发效率上明显优于基于SUN J2EE框架开发的PETSHOP. 项目包括所有源码及数据库建库脚本,是不错的学习 AS

/******************************************************************************
**        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)