分页的存储过程

      最近在做一些电子商务网站的系统，在对商品进行管理的时候，碰到了些问题在一页显示不完商品时，我们往往的会用到分页，在我以前我总是用手动的去分页，这样会损耗大量的系统资源，不能精确获取到我们想要的信息！为了解决此问题，我就用到了分页的存储过程，把它拿出来和大家分享下，希望能帮助到需要帮助的人！下面用一个我用到的例子来说明吧。

 

CREATE PROCEDURE GetProductsOnDepartmentPromotion
     (
        @DepartmentID INT,
        @DescriptionLength INT,
        @PageNumber INT,
        @ProductsPerPage INT,
        @HowManyProducts INT OUTPUT
     )
AS

 

--创建一张临时表
DECLARE @Products TABLE
  (
 RowNumber INT,
  ProductID INT,
  Name VARCHAR(50),
  Description VARCHAR(5000),
  Price MONEY,
  Image1FileName VARCHAR(50),
  Image2FileName VARCHAR(50),
  OnDepartmentPromotion BIT,
  OnCatalogPromotion BIT)

--向临时表中插入数据ROW_NUMBER() 是sqlsever2005系统函数
INSERT INTO @Products 
/*子查询*/ 
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row,
       ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
       Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM
(
 --表的内连接
  SELECT DISTINCT Product.ProductID, Product.Name,

  --截取字符串
  SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description,
  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM Product INNER JOIN ProductCategory
                      ON Product.ProductID = ProductCategory.ProductID
              INNER JOIN Category
                      ON ProductCategory.CategoryID = Category.CategoryID
   WHERE Product.OnDepartmentPromotion = 1
   AND Category.DepartmentID = @DepartmentID
)

 AS ProductOnDepPr --表的别名

 

--为输出参数赋值

SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

--实现分页的功能
SELECT ProductID, Name, Description, Price, Image1FileName,
       Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM @Products
  WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
  AND RowNumber <= @PageNumber * @ProductsPerPage

GO