分页存储过程(代码)

use BalloonShop
select * from Product


--20个字,1, 5
CREATE PROCEDURE GetProductsOnCatalogPromotion
(@DescriptionLength INT,--描述信息长度
@PageNumber INT, -- 第几页
@ProductsPerPage INT, --每页显示几个商品
@HowManyProducts INT OUTPUT -- 一共有多少商品
)
AS
-- declare a new TABLE variable
DECLARE @Products TABLE --表变量
(RowNumber INT, --在products原始表上增加一个可靠的编号字段
 ProductID INT,
 Name VARCHAR(50),
 Description VARCHAR(5000),
 Price MONEY,
 Image1FileName VARCHAR(50),
 Image2FileName VARCHAR(50),
 OnDepartmentPromotion BIT,
 OnCatalogPromotion BIT)

-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
       ProductID, Name, 
       SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
       Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1

-- 给输出参数@HowManyProducts赋值
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

-- extract the requested page of products
-- 把请求的第几页的内容从@Products表变量中查询出来
SELECT ProductID, Name, Description, Price, Image1FileName,
       Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
  AND RowNumber <= @PageNumber * @ProductsPerPage
GO

use BalloonShop

--理解表变量
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)
INSERT INTO @Products    
SELECT  Row_number() OVER (ORDER BY ProductID) , * from Product where OnCatalogPromotion = 1

select * from @Products
go

use BalloonShop
declare @HowManyProducts int
exec GetProductsOnCatalogPromotion 15,2,6, @HowManyProducts out 
select @HowManyProducts

--一共多少页:总商品数/每页的产品数 (小数) = 2.1
-- ceiling 

 

转载于:https://www.cnblogs.com/fllowerqq/p/8970594.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值