create procedure 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;
当然,SqlServer中并不只有这一种写法,只是这种写法是比较常见而已。