/*
** procedure getting records from table Employee 
*/
CREATE  PROCEDURE usp_GetAllEmployees
@OrderByField varchar(50) = NULL,
@OrderByAsc bit = 1,
@FirstRecordToDisplay int = NULL,
@NumberOfRecordsToDisplay int = NULL
AS

SET NOCOUNT ON
DECLARE @TotalCount int
IF OBJECT_ID('tempdb..#tmpGetEmployee') IS NOT NULL DROP TABLE #tmpGetEmployee
CREATE TABLE #tmpGetEmployee
(
tmpGetID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
EmployeeID int,
FromDate datetime,
FirstName varchar(50), 
LastName varchar(50), 
EmailAddress varchar(255), 
)
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
SET @TotalCount = @@ROWCOUNT
DECLARE @k smallint
DECLARE @NewFirstRecord int
IF @OrderByAsc = 0
BEGIN
 SET @k = -1
 SET @NewFirstRecord = @TotalCount - ISNULL(@FirstRecordToDisplay, 1) + 1
END
ELSE
BEGIN
 SET @k = 1
 SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
END

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

GO