Paging for pedigree extraction - Horse Paging 1/3

 
/****** Object:  StoredProcedure [dbo].[HorsesPaging]    Script Date: 10/31/2009 13:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[HorsesPaging]
	-- Add the parameters for the stored procedure here
	-- exec [dbo].[HorsesPaging] 1000, 20
	@StartRow bigint=1,
	@PageSize int=15,
	@NumeroTotaleRecord  bigint out
AS
BEGIN


DECLARE @HorseID bigInt


create table #tempHorses  (
    HorseID bigint NOT NULL PRIMARY KEY
)


SELECT @NumeroTotaleRecord=COUNT(*) FROM Horses



DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY  FOR

SELECT HorseID FROM Horses --ORDER BY SortColumn


OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @HorseID

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
    INSERT #tempHorses(HorseID) VALUES(@HorseID)
    
    FETCH NEXT FROM PagingCursor INTO @HorseID
    SET @PageSize = @PageSize - 1
END

close PagingCursor
deallocate PagingCursor


SELECT * FROM vwHorses
inner JOIN #tempHorses temp ON vwHorses.HorseID = temp.HorseID
-- ORDER BY SortColumn

drop table #tempHorses




return @numerototalerecord

end
GO