create procedure [dbo].[GetProductByCategoryId]( @CategoryID int, @PageIndex int = 0, @PageSize int = 20, @TotalRecords int output ) as begin declare @ResultTable table ( RowIndex int, ProductID int, ProductName nvarchar(50), CategoryID int, Unit nvarchar(10), UnitPrice money, Quantity int ); insert into @ResultTable select row_number() over (order by ProductID asc) as RowIndex, p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity from Products as p where CategoryID = @CategoryID; select @TotalRecords = count(*) from @ResultTable; select * from @ResultTable where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1)); end;
SQLServer2005 row_number() 分页存储过程
最新推荐文章于 2020-11-13 16:52:13 发布