upproductgetlist_listbypage_benchmark.sql

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

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