The best code examples of the use of the DNN pager user control are found in the admin displays of users and vendors.  Look also at how the GetUsers and GetVendors stored proceedures return sets of records based on the pagesize and pageindex.  If you choose to use the DNN pager, one of the keys to its proper use is to pass in ALL the parameter key/value pairs needed by your module. Although I like the appearance of the DNN pager and have used it several times to follow usual DNN practices and although it does have the advantage of exposing all parameters in the query string so that a user can set a favorite or copy a link to be able to return to a specific page of data, I really prefer using custom or automatic paging in the GridView/DataGrid better.

Regarding techniques for paging in the sproc, I really like the use of the new ROW_Number and Select Top (@N) capabilites in SQL Server 2005 over the alternative of using a temporary table and dynamic SQL in SQL Server 2000.  Again this brings up a question I posed in another thread - do we start writing our sprocs to take advantage of the features of SQL 2005 and indicate to users that the module requires SQL 2005 or continue writing more compatible SQL 2000 code? 

I thought that I had replyed to your post yesterday, but for some reason, the post is not showing, so here it is again . . .

The sproc's developer chose to return TotalRecords as a second result set rather than as an output parameter. This seems to be the usual pattern used throughout the DNN core sprocs - probably so that the SqlDataProvider/DataProviders consistently return only an IDataReader.  How TotalRecords is obtained from the second result set of the IDataReader can be seen in the controller class. Note the use of  IDataReader.NextResult() to advance to the second result set. Here is a bit of code from the controller class of a module currently under development that follows this same pattern to obtain a paged set of records and the TotalRecords count:

Public Function GetRequests(ByVal ModuleID As Integer, ByVal Filter As String, ByVal OrderBy As String, ByVal UserID As Integer, _
                                    ByVal PageSize As Integer, ByVal PageIndex As Integer, ByRef TotalRecords As Integer) As List(Of RequestInfo)

            Dim dr As IDataReader = DataProvider.Instance().GetRequests(ModuleID, Filter, OrderBy, UserID, PageSize, PageIndex)
            Dim objRequests As New List(Of RequestInfo)
            If Not dr Is Nothing Then
                Try
                    While dr.Read
                        objRequests.Add(FillRequest(dr, False))
                    End While
                    Try
                        dr.NextResult()
                        If dr.Read Then
                            TotalRecords = Convert.ToInt32(dr("TotalRecords"))
                        Else
                            TotalRecords = objRequests.Count
                        End If
                    Catch ex As Exception
                        TotalRecords = objRequests.Count
                    End Try
                Finally
                    If Not dr.IsClosed Then dr.Close()
                End Try
            End If
            Return objRequests
        End Function

The FillRequest method is my custom hydrator for the RequestInfo class. For performance reasons, I prefer not to use CBO.FillObject.