ALTER PROCEDURE [dbo].[GetUserInfoPaged]
-- Add the parameters for the stored procedure here
@startRowIndex int,
@maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT ID, UserName, Password,Age FROM
(SELECT ID, UserName, Password,Age , ROW_NUMBER() OVER(ORDER BY ID asc/desc) AS UserRank FROM UserInfo )
AS UserInfoWithRowNumberWHERE where
UserRank >= (@startRowIndex-1)*@maximumRows +1 AND UserRank <= (@startRowIndex * @maximumRows)
END
---------------------------------------------------------
CREATE PROCEDURE GetProductionOrder
@StartRowIndex int,
@MaximumRows int,
@SortParam nvarchar(50)
AS
BEGIN
declare @Sql nvarchar(2000);
set @Sql = 'with TempTable as(
select ROW_NUMBER() OVER (ORDER BY ' + @SortParam + ') AS RowNumber,
p.ProductionOrderDetailID,ProductionOrderNO, s.StockID,Quantity,ConfirmedShipmentDate,[State],OSStockNo
from ProductionOrderDetail p inner join Stock s on p.StockID = s.StockID)
select * from TempTable
where RowNumber between ' + cast(@StartRowIndex + 1 as varchar(50)) + ' and ' + cast(@StartRowIndex + @MaximumRows as varchar(50));
exec(@Sql);
END
http://www.cnblogs.com/ilovejolly/archive/2006/10/10/523992.html
每页4条数据，共13条数据 1,4 5,8 9,12 13,13