And finally, this is what the GET_EMPLOYEES SP looks like:

Code:
CREATE PROCEDURE Get_Employees( @CurrentPage int,
     @PageSize int,
     @TotalRecords int OUTPUT)

AS

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable
(
 EmpId int IDENTITY PRIMARY KEY,
 fname varchar(20),
 lname varchar(30),
 pub_id char(4),
 hire_date datetime
)

--Fill the temp table with the reminders
Insert Into #TempTable 
(
 fname,
 lname,
 pub_id,
 hire_date
)
Select  fname,
  lname,
  pub_id,
  hire_date
From  employee
Order By lname

--Select one page of data based on the record numbers above
Select fname,
 lname,
 pub_id,
 hire_date
From #TempTable
Where EmpId > @FirstRec 
And EmpId < @LastRec

--Return the total number of records available as an output parameter
Select @TotalRecords = Count(*)
From #TempTable
GO