ALTER procedure [dbo].[sp_ProductGetListInCategory]
------------------------------------
--目的：按分类Id获取产品列表。
--
--维护日志
--
--维护人                维护时间                描述
-------------------        -------------------        ------------------------
--xuyiwei                2012-7-14                创建
------------------------------------
--当前页码
@PageNumber INT,
--每页多少
@PerPage INT,
--类别Id
@CategoryId INT,
--详情长度设定
@DetailLength INT,
--返回总共多少行
@HowMany INT OUTPUT

AS
-- 声明新的TABLE变量
DECLARE @Product TABLE
(
 RowNumber INT,
 Id INT,
 Title NVARCHAR(100),
 CreateTime DateTime,
 ModifiedTime DateTime,
 Pic NVARCHAR(100),
 ListPrice MONEY,
 Price MONEY
 )

-- 用完整的产品列表填充该TABLE变量
INSERT INTO @Product
--SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
       Product.Id, Title, CreateTime, ModifiedTime, Pic, ListPrice, Price
FROM Product INNER JOIN ProductCategory
  ON Product.Id = ProductCategory.ProductId
WHERE ProductCategory.CategoryId = @CategoryId

-- 使用OUTPUT变量返回总共多少行
SELECT @HowMany = COUNT(Id) FROM @Product

-- 获取请求的商品页面
SELECT Id, Title, CreateTime, ModifiedTime, Pic, ListPrice, Price
FROM @Product
WHERE RowNumber > (@PageNumber - 1) * @PerPage
  AND RowNumber <= @PageNumber * @PerPage
  

INSERT INTO @Product 
SELECT  
       Product.Id, Title, CreateTime, ModifiedTime, Pic, ListPrice, Price
FROM Product INNER JOIN ProductCategory
  ON Product.Id = ProductCategory.ProductId
WHERE ProductCategory.CategoryId = @CategoryId
order by Product.Id