A good friend of mine, Grundle, helped me figure out a pretty slick custom paging procedure for use in sql 2005 ( don't think variables in select top or ROW_COUNT() work in any lower versions)
here's the procedure, i'll do up a bit more on the data access layer changes (they're minor, basically adding a row number field to the PersonContact object of my adventureworks service)
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[GetPersonContact] Script Date: 05/10/2007 21:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[GetPersonContactByName]
(
@length int,
@lastrow int,
@lastname nvarchar(50)
)
AS
declare @totalrows int
set @totalrows = ( SELECT COUNT(*) from Person.Contact where LastName like (@lastname));
/* SET NOCOUNT ON */
WITH Ordered AS (SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS rownum, ContactID, NameStyle, Title, FirstName, MiddleName,
LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate
FROM Person.Contact where LastName like (@lastname) )
SELECT TOP (@length) *
FROM Ordered
WHERE rownum > @lastrow
return @totalrows
