Blog Paging via T-SQL Stored Proc

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