存储过程分页

--查询推荐分类中的商品
CREATE PROCEDURE CatalogGetProductsOnFrontPromo
(
	@DescriptionLength INT,
	@PageNumber INT,
	@ProductsPerPage INT,
	@HowManyProducts INT OUTPUT	
)
AS
--声明新的Table变量
DECLARE @Products TABLE
(
	RowNumber INT,
	ProductID INT,
	Name	  NVARCHAR(50),
	Description NVARCHAR(MAX),
	Price	  MONEY,
	Thumbnail NVARCHAR(50),
	Image	  NVARCHAR(50),
	PromoFront BIT,
	PromoDept  BIT
)
--用完整的商品列表填充TABLE变量
INSERT INTO @Products
SELECT ROW_NUMBER() OVER(ORDER BY Product.ProductID),
	   ProductID,Name,
	   CASE WHEN LEN(Description)<=@DescriptionLength THEN Description
		    ELSE SUBSTRING(Description,1,@DescriptionLength)+'...'END
	   AS Description,Price,Thumbnail,Image,PromoFront,PromoDept
FROM Product
WHERE PromoFront=1

--使用Output变量返回商品总数
SELECT @HowManyProducts=COUNT(ProductID) FROM @Products

--获取请求的商品页面
SELECT ProductID,Name,Description,Price,Thumbnail,Image,PromoFront,PromoDept 
FROM @Products
WHERE RowNumber>(@PageNumber-1)*@ProductsPerPage And RowNumber<=@PageNumber*@ProductsPerPage