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...

ALTER proc GetPicturePage

(

@Page int,

@PageSize int

)

as

begin

   set 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

end