GridView自定义分页的四种存储过程

来源：blog.csdn.net 作者：高歌出处：巧巧读书 2008-07-16 进入讨论组

今天有空总结了一下使用存储过程对GridView进行分页的4种写法(分别是使用Top关键字，临时表，临时表变量和SQL Server 2005 新加的Row_Number()函数)

         if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')
        drop proc GetProductsCount
        go
        CREATE PROCEDURE GetProductsCount
        as
        select count(*) from products
        go

--1.使用Top

         if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
        drop proc GetProductsByPage
        go
        CREATE PROCEDURE GetProductsByPage
        @PageNumber int,
        @PageSize int
        AS
        declare @sql nvarchar(4000)
        set @sql = 'select top ' + Convert(varchar, @PageSize)
          + ' * from products where productid not in (select top ' + Convert(varchar, (@PageNumber - 1) * @PageSize) + ' productid from products)'
        exec sp_executesql @sql
        go
        --exec GetProductsByPage 1, 10
        --exec GetProductsByPage 5, 10

--2.使用临时表

-- 创建临时表

         CREATE TABLE #TempProducts
        (
        ID int IDENTITY PRIMARY KEY,
        ProductID int,
        ProductName varchar(40) ,
        SupplierID int,
        CategoryID int,
        QuantityPerUnit nvarchar(20),
        UnitPrice money,
        UnitsInStock smallint,
        UnitsOnOrder smallint,
        ReorderLevel smallint,
        Discontinued bit
        )

-- 填充临时表

         INSERT INTO #TempProducts
        (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
        SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
        FROM Products
        DECLARE @FromID int
        DECLARE @ToID int
        SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
        SET @ToID = @PageNumber * @PageSize
        SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
        FROM #TempProducts
        WHERE ID >= @FromID AND ID <= @ToID
        go
        --exec GetProductsByPage 1, 10
        --exec GetProductsByPage 5, 10

--3.使用表变量

为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.

当SET ROWCOUNT的值指定为PageNumber * PageSize时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多

--在返回指定的行数之后停止处理查询

         SET ROWCOUNT @maxRows
        INSERT INTO @TempProducts (productid)
        SELECT productid
        FROM products
        ORDER BY productid
        SET ROWCOUNT @PageSize
        SELECT p.*
        FROM @TempProducts t INNER JOIN products p
        ON t.productid = p.productid
        WHERE ID > (@PageNumber - 1) * @PageSize
        SET ROWCOUNT 0
        GO
        --exec GetProductsByPage 1, 10
        --exec GetProductsByPage 5, 10