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

        今天有空总结了一下使用存储过程对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.使用临时表          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

        -- 创建临时表          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时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多

        */          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 @TempProducts TABLE
        (
           ID int IDENTITY,
           productid int
        )
        DECLARE @maxRows int
        SET @maxRows = @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