Listing 4: SELECT_WITH_PAGING Stored Procedure
CREATE PROCEDURE SELECT_WITH_PAGING (
   @strFields varchar(4000),
   @strPK varchar(100),
   @strTables varchar(4000),
   @intPageNo int = 1,
   @intPageSize int = NULL,
   @blnGetRecordCount bit = 0,
   @strFilter varchar(8000) = NULL,
   @strSort varchar(8000) = NULL,
   @strGroup varchar(8000) = NULL)

/* Executes a SELECT statement that the parameters define,and returns a particular page of data (or all 
rows) efficiently. */
 AS
   DECLARE @blnBringAllRecords bit
   DECLARE @strPageNo varchar(50)
   DECLARE @strPageSize varchar(50)
   DECLARE @strSkippedRows varchar(50)
   DECLARE @strFilterCriteria varchar(8000)
   DECLARE @strSimpleFilter varchar(8000)
   DECLARE @strSortCriteria varchar(8000)
   DECLARE @strGroupCriteria varchar(8000)
   DECLARE @intRecordcount int
   DECLARE @intPagecount int

/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will 
set a flag that will help avoid paging (blnBringAllRecords). */
   IF @intPageNo < 1
      SET @intPageNo = 1
   SET @strPageNo = CONVERT(varchar(50), @intPageNo)
   IF @intPageSize IS NULL OR @intPageSize < 1         -- Bring all records, don't do paging.
      SET @blnBringAllRecords = 1
   ELSE
      BEGIN
         SET @blnBringAllRecords = 0
         SET @strPageSize = CONVERT(varchar(50), @intPageSize)
         SET @strPageNo =  CONVERT(varchar(50), @intPageNo)
         SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
      END

/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting, respectively, by executing more efficient 
queries. */
   IF @strFilter IS NOT NULL AND @strFilter != ''
      BEGIN
         SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
         SET @strSimpleFilter = ' AND ' + @strFilter + ' '
      END
   ELSE
      BEGIN
         SET @strSimpleFilter = ''
         SET @strFilterCriteria = ''
      END
   IF @strSort IS NOT NULL AND @strSort != ''
      SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
   ELSE
      SET @strSortCriteria = ''
   IF @strGroup IS NOT NULL AND @strGroup != ''
      SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
   ELSE
      SET @strGroupCriteria = ''

/* Now start doing the real work. */
IF @blnBringAllRecords = 1                   -- Ignore paging and run a simple SELECT.
BEGIN

   EXEC (
      'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
         @strGroupCriteria + @strSortCriteria
)

END                                                          -- We had to bring all records.
ELSE                                                        -- Bring only a particular page.
BEGIN
   IF @intPageNo = 1                                -- In this case we can execute a more efficient
                                                                  -- query with no subqueries.
      EXEC (
         'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
            @strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
   ELSE                                                     -- Execute a structure of subqueries that brings the correct page.
      EXEC (
         'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
            (SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
               ' WHERE' + @strPK + 'NOT IN' + '
                  (SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
                      @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
               @strSimpleFilter +
               @strGroupCriteria +
               @strSortCriteria + ') ' +
            @strGroupCriteria +
            @strSortCriteria
)
END                                                         -- We had to bring a particular page.

/* If we need to return the recordcount: */
IF @blnGetRecordCount = 1
   IF @strGroupCriteria != ''
      EXEC (
         'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
            @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
   ELSE
      EXEC (
         'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria +
            @strGroupCriteria
)
GO