select * from
(
select *, ROW_NUMBER() OVER (order by SORTEDBY) as Pos from TABLENAME
) as T
where T.Pos > @StartIndex and T.Pos < (@startIndex + @maxRows)
create PROCEDURE [dbo].[GetPagedLists]
@Category NVarChar(25),
@StartRowIndex INT,
@NumRows INT
AS
BEGIN
WITH ListEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY ListID ASC)AS Row, ListID, [Name], Category FROM Lists where Category=@Category)
SELECT ListID, [Name], Category
FROM ListEntries
WHERE Row between @StartRowIndex and @StartRowIndex+@NumRows
END
