It's around two a.m. on a Sunday morning. I am finishing off a bowl of Frankenberry after completing some work to incorporate paging into my photoblog site. At first I thought I'd handle the paging at the application level and just do a simple query and find the row range that I need, but ultimately I resisted because a) it's in efficient and b) I should learn how to do this in SQL to add it to my bag of tricks. I searched and found a stored proc by Anatoly Lubarsky, which works well when paging by ID and in ascending order. I modified it to work in reverse chronological order (by the date my photos were taken), which I think is handy for geeks like me building our own weblog tools. Anyhow, I thought I'd share...
beginset nocount on declare @TotalRowsNum int declare @FirstSelectingRowNum int declare @TakenDateTime datetime select @TotalRowsNum = count(PictureId) from Pictures where IsDeleted = 0 select @FirstSelectingRowNum = ((@Page - 1) * @PageSize) + 1 if (@FirstSelectingRowNum <= @TotalRowsNum) begin set rowcount @FirstSelectingRowNum select @TakenDateTime = TakenDateTime from Pictures where IsDeleted = 0 order by TakenDateTime desc, ImageOriginal desc set rowcount @PageSize select * from Pictures where TakenDateTime <= @TakenDateTime and IsDeleted = 0 order by TakenDateTime desc, ImageOriginal desc end set nocount off