Thursday, September 21, 2006

Paging In TSQL

There's a lot of crap written about paging in TSQL (SQL Server's SQL). Then I found this article that uses SET ROWCOUNT to do it. Brilliant.
However, there's no error checking, so I've tweaked it a bit, and my results are below. Enjoy.

DECLARE @Start int, @End int

DECLARE @StartId int
DECLARE @NumRows int

SELECT @Start = 11, @End = 20

IF @Start <= @End
BEGIN
SET ROWCOUNT @Start
SELECT @StartId = addressid FROM Person.Address
order by addressid Asc

IF @@ROWCOUNT >= @Start
BEGIN

SELECT @NumRows = ( @End - @Start ) + 1

SET ROWCOUNT @NumRows

SELECT * FROM Person.Address
WHERE addressid >= @startId
order by addressid Asc
END
SET ROWCOUNT 0
END

No comments: