create PROCEDURE staffList @pageNum int = -1, @pageSize int, @totalHits int OUTPUT, @totalPages int OUTPUT as

DECLARE @start int ,@end int ,@err int

-- This table holds results of the query and assigns a record number 
DECLARE @results TABLE (
  recnum int IDENTITY(1,1),username char(8), deptName varchar(50), posName varchar(50), fname varchar(50),
  lname varchar(50), email varchar(50), listed bit, access bit)

-- write the results to the table variable so that each row is assigned to a recnum
INSERT INTO @results (username, deptName, posName, fname, lname, email, listed, access)
  SELECT s.username, d.deptName, p.posName, s.fname, s.lname, s.email, s.listed, s.access
  FROM dbo.staff s inner join dbo.department d on s.deptID = d.deptID inner join dbo.position p on s.posID = p.posID 
  ORDER BY s.lname, s.fname

SELECT @err = @@ERROR, @totalHits = @@ROWCOUNT

select @totalPages =  ceiling(cast(@totalHits as decimal)/ cast(@pageSize as decimal))

IF @err != 0 RETURN -1

-- calculate the beginning and ending row numbers
SET @end = @pageNum * @pageSize
SET @start = @end - @pageSize + 1

-- Return the requested page unless @pageNum = -1, in which case, return the entire result set
SELECT username, deptName, posName, fname, lname, email, listed, access
FROM @results
WHERE (@pageNum = -1 OR recnum BETWEEN @start AND @end)

IF @@ERROR != 0 RETURN -1
ELSE RETURN 0