A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the old fashioned database paging solution.

This paging solution initially grabs a subset of a table and counts the records. It then stores ordered results based on the parameter passed into the common table expression. Additional parameters are the number of rows the caller wants on each page and the page number the caller is currently retrieving.

CREATE PROCEDURE dbo.GetEmployees
(
		@SortColumn VARCHAR(50) = null,
		@iRows INT = 10,
		@iPageNum INT = 1
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @RecordCount int
DECLARE @iNbrPages int

SET @RecordCount = 0
SET @iNbrPages = 0

SELECT
	emp.EmployeeID,
	emp.FirstName,
	emp.LastName,
	emp.DateHired
INTO #Employees
FROM HR.Employees emp
WHERE emp.IsTerminated = 1

SELECT
	@iNbrPages = CEILING(COUNT(1)/(@iRows*1.0)),
	@RecordCount = COUNT(1)
FROM #Employees

BEGIN

	;WITH PagingCTE
	(
		Row_ID,
		EmployeeID,
		FirstName,
		LastName,
		DateHired
	)
	AS
	(
		SELECT
			ROW_NUMBER()
			OVER(ORDER BY
			CASE WHEN @SortColumn = 'EmployeeID'	THEN emp.EmployeeID						END ASC,
			CASE WHEN @SortColumn = 'FirstName'		THEN emp.FirstName						END ASC,
			CASE WHEN @SortColumn = 'LastName'		THEN emp.LastName						END ASC,
			CASE WHEN @SortColumn = 'DateHired'		THEN emp.DateHired						END ASC
			) AS [Row_ID],
			emp.EmployeeID,
			emp.FirstName,
			emp.LastName,
			emp.DateHired
		FROM #Employees emp
	) 

	SELECT
		emp.EmployeeID,
		emp.FirstName,
		emp.LastName,
		emp.DateHired,
		PageNumber = @iPageNum,
		TotalNbrPages = @iNbrPages,
		TotalRecords = @RecordCount
	FROM PagingCTE emp
	WHERE Row_ID <= (@iRows * @iPageNum) - (@iRows - 1)
	AND Row_ID <=  @iRows * @iPageNum

END

A more efficient way to do this would be to pass in a sessionID unique to the user, then create a temp table based upon that users sessionID. Then on each call to the procedure, the procedure could check for the existence of the temp table so the result would not have to be queried again.