分页的存储过程

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

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

秒客网

分页的存储过程

相关文章