Pagination and Sorting in MS SQL Server 2000

/*
** procedure getting records from table Employee 
*/
CREATE PROCEDURE usp_GetAllEmployees
-- parameters for pagination and sorting
@OrderByField varchar(50) = NULL,
@OrderByAsc bit = 1,
@FirstRecordToDisplay int = NULL,
@NumberOfRecordsToDisplay int = NULL
-- end of the parameters for pagination and sorting
AS
SET NOCOUNT ON
DECLARE @TotalCount int -- this variable we are gonna use later to see the total number of the records in the table

-- here we need to create a temporary table from which we will be getting a final recordset
IF OBJECT_ID('tempdb..#tmpGetEmployee') IS NOT NULL DROP TABLE #tmpGetEmployee

-- the temporary table has to have all the fields of the base table and one extra-field wich is auto-increment and a primary key
-- we are gonna use that field for sorting and paging data
CREATE TABLE #tmpGetEmployee
(
	tmpGetID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	EmployeeID int,
	FromDate datetime,
	FirstName varchar(50),
	LastName varchar(50),
	EmailAddress varchar(255)
)

-- then the temporary table is being filled from the base one with sorted data
INSERT #tmpGetEmployee(EmployeeID, FromDate, FirstName, LastName, EmailAddress)
SELECT EmployeeID, EmployeeFromDate, FirstName, LastName, EmailAddress
FROM [Employee]
ORDER BY
CASE WHEN @OrderByField = 'EmployeeID' THEN EmployeeID ELSE NULL END,
CASE WHEN @OrderByField = 'FromDate' THEN FromDate ELSE NULL END,
CASE WHEN @OrderByField = 'FirstName' THEN FirstName ELSE NULL END,
CASE WHEN @OrderByField = 'LastName' THEN LastName ELSE NULL END,
CASE WHEN @OrderByField = 'EmailAddress' THEN EmailAddress ELSE NULL END
-- now the temporary table contains all the sorted data from the base table
-- but the direction of sorting has not been considered
-- that is what we gonna do-- getting total number of the records
SET @TotalCount = @@ROWCOUNT

-- coefficient of direction (asc -> @k = 1; desc - > @k = -1)
DECLARE @k smallint

-- the variable that should store tmpGetID value from the table #tmpGetEmployee -
-- which record from this table should be displayed first depending on the sorting direction
DECLARE @NewFirstRecord int
IF @OrderByAsc = 0
BEGIN
	SET @k = -1
	-- if the direction is ASC we should display the data starting from the bottom of the table
	SET @NewFirstRecord = @TotalCount - ISNULL(@FirstRecordToDisplay, 1) + 1
END
ELSE
BEGIN
	SET @k = 1
	SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
END

-- final recordset - a bit confusing, but it works!
SELECT
EmployeeID, FromDate, FirstName, LastName, EmailAddress
FROM #tmpGetEmployee
WHERE (@FirstRecordToDisplay IS NULL OR (tmpGetID - @NewFirstRecord) * @k >= 0 )
AND (@NumberOfRecordsToDisplay IS NULL OR (@NewFirstRecord + @k * @NumberOfRecordsToDisplay - tmpGetID) * @k > 0)
ORDER BY @k * tmpGetID