分页存储过程(老是提醒0附近有语法错误)
CREATE procedure GetProductsInCategoryintCount
(@CategoryID Int,
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(ProductID) from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select ProductID from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID where productcategory.categoryid = @categoryid order by ProductID desc
select O.* from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID O,@indextable t where O.ProductID=t.nid
and t.id> @PageLowerBound and t.id <=@PageUpperBound order by t.id
end
set nocount off
GO
[解决办法]
select O.* from Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID O,@indextable t where O.ProductID=t.nid
and t.id> @PageLowerBound and t.id <=@PageUpperBound order by t.id
-- 改为
select O.*
from Product
INNER JOIN ProductCategory O
ON Product.ProductID = ProductCategory.ProductID
INNER JOIN @indextable t
ON O.ProductID=t.nid
WHERE t.id> @PageLowerBound and t.id <=@PageUpperBound
order by t.id
