Paging for pedigree extraction - Horse Paging 3/3

 
/****** Object:  StoredProcedure [dbo].[HorsesPaging3]    Script Date: 10/31/2009 13:03:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[HorsesPaging3]
(
 @ActivePage  BigInt = 1,
 @PageCount  BigInt = 10,
 @TotalPage bigint output
)
AS
Begin

--test
--declare @tot bigint
--exec  HorsesPaging3 2666, 10, @tot output
--select @tot

--Declare @StartDate as DateTime
--SET @StartDate = GetDate()

--DECLARE @MidRow as BigInt
DECLARE @TotalRow as BigInt
--DECLARE @TotalPage as BigInt
DECLARE @MidRow as BigInt
Select @TotalRow = Count(HorseID) From Horses WITH (NOLOCK)
SET @MidRow = Round(@TotalRow/2,0)
SET @TotalPage = Round(@TotalRow / @PageCount,0)
IF @TotalRow > (@TotalPage * @PageCount) SET @TotalPage = @TotalPage + 1

DECLARE @RowPos as BigInt
DECLARE @ID as VarChar(100)
SET @RowPos = (@PageCount * @ActivePage) - @PageCount
IF @RowPos > @MidRow
Begin
SET @RowPos = @TotalRow - @RowPos 
SET ROWCOUNT @ROWPOS
SELECT @ID = HorseID From Horses WITH (NOLOCK) --ORDER BY PartNumber DESC
END
ELSE
Begin
SET @RowPos = @PageCount * @ActivePage
SET @RowPos = @RowPos - @PageCount + 1
SET ROWCOUNT @ROWPOS
SELECT @ID = HorseID From Horses WITH (NOLOCK) --ORDER BY PartNumber ASC
End

SET ROWCOUNT @PageCount
create table #tempHorses(HorseID bigint not null primary key)

INSERT INTO #tempHorses 

SELECT Top (@PageCount) HorseID FROM Horses WITH (NOLOCK) 
WHERE HorseID>= @ID --ORDER BY PartNumber ASC

--UPDATE @Part SET TotalPage = @TotalPage

SELECT * FROM vwHorses inner join
#tempHorses on vwHorses.HorseID= #tempHorses.HorseID -- Order By PartNumber

--SELECT DateDiff(MilliSecond,@StartDate,GetDate())

END

GO