A Simple Single Generic Stored Procedure to return dataset from any table in a database with paging. 

USE [DFPS]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetPagingData]    Script Date: 09/22/2011 05:00:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_GetPagingData]
@FilterText nvarchar(max) = null,
@PageSize [int] = 10,
@SortOrder nvarchar(max) = N'ID',
@ObjectName nvarchar(50),
@CurrentPage int OUTPUT,
@PageCount int OUTPUT
AS
BEGIN
DECLARE @sqlStr NVARCHAR(max)
DECLARE @Count numeric(12,2)
DECLARE @rowStartIdx int
if @CurrentPage is null
set @CurrentPage = 0

SET @sqlStr = N'SELECT @CountParam = COUNT(*) FROM '+ @ObjectName + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END

EXEC sp_executesql @sqlStr, N'@CountParam int OUTPUT', @Count OUTPUT

if ( not (@CurrentPage <> 0 and CEILING(@Count / @PageSize) >= @CurrentPage))
set @CurrentPage = (case when @Count > 0 then 1 else 0 end)

SET @PageCount = (case when @Count > @PageSize then CEILING(@Count / @PageSize) else
(case when @Count > 0 then 1 else 0 end) end)

SET @rowStartIdx = 1 + (@PageSize * (CASE WHEN @CurrentPage = 0 THEN 0 ELSE @CurrentPage - 1 END))

SET @sqlStr = N'WITH Entries AS
(SELECT *'
+ CASE WHEN @SortOrder IS NOT NULL THEN ', ROW_NUMBER() OVER (ORDER BY ' + @SortOrder + ') AS RowNum'
 ELSE 'ID'
 END
+ ' FROM '+ @ObjectName
+ CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
+ ')
SELECT * FROM Entries WHERE (RowNum BETWEEN ' + CAST(@rowStartIdx AS nvarchar) + ' AND ' +
CAST(@rowStartIdx - 1 + ISNULL(@PageSize,0) AS nvarchar) + ')'

EXEC sp_executesql @sqlStr
END