upproductsearch_listbypage_benchmark.sql

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

/******************************************************************************
**        File: upProductSearch_ListByPage_Benchmark.sql
**        Name: upProductSearch_ListByPage Stored Procedure
**        Desc: Basic catalog search. 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 25 results for the benchmarking.
**
**        Date: 11/9/2001
**
*******************************************************************************/

ALTER PROCEDURE upProductSearch_ListByPage
(
    @searchText          varchar(255),
    @nCurrentPage        int,
    @nPageSize           int,
    @nSearchResults      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 #SearchResultsTempTable
    (
        rowid               int           IDENTITY PRIMARY KEY,
        productid           char(10)               NOT NULL,
        [name]              varchar(80)            NULL,
        descn               varchar(255)           NULL
    )

    -- copy the search results into the temporary table    
    DECLARE @_name varchar(50) 
    SET @_name = '%' + @searchText + '%'	
    INSERT INTO #SearchResultsTempTable (productid, [name], descn)
    SELECT TOP 25 productid, [name], descn
    FROM Product
    WHERE 
    (
        --search the product name field
       [name] like (@_name)
       OR
       
       -- search the products category field
       category like  (@_name)
    )
        
    -- always return the total number of items found in the search
    SELECT @nSearchResults = @@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], descn
    FROM #SearchResultsTempTable
    WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)