- SQL code
ALTER procedure [dbo].[sp_ProductGetListInCategory]--------------------------------------目的：按分类Id获取产品列表。----维护日志----维护人 维护时间 描述------------------- ------------------- --------------------------xuyiwei 2012-7-14 创建--------------------------------------当前页码@PageNumber INT,--每页多少@PerPage INT,--类别Id@CategoryId INT,--详情长度设定@DetailLength INT,--返回总共多少行@HowMany INT OUTPUTAS-- 声明新的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, PriceFROM Product INNER JOIN ProductCategory ON Product.Id = ProductCategory.ProductIdWHERE ProductCategory.CategoryId = @CategoryId-- 使用OUTPUT变量返回总共多少行SELECT @HowMany = COUNT(Id) FROM @Product-- 获取请求的商品页面SELECT Id, Title, CreateTime, ModifiedTime, Pic, ListPrice, PriceFROM @ProductWHERE RowNumber > (@PageNumber - 1) * @PerPage AND RowNumber <= @PageNumber * @PerPage
------解决方案--------------------
在你的基础上如下修改：
表变量的定义中：
RowNumber INT,
改成
RowNumber INT identity(1,1)
然后把Row_Number()去掉，在最后面加上
Order by Product.Id
即：
- SQL code
INSERT INTO @Product SELECT Product.Id, Title, CreateTime, ModifiedTime, Pic, ListPrice, PriceFROM Product INNER JOIN ProductCategory ON Product.Id = ProductCategory.ProductIdWHERE ProductCategory.CategoryId = @CategoryIdorder by Product.Id