SP Works But I Need Help In Custom Paging

I need to be able to specify which column to sort by, BUT SQL 2000 does not allow me to





SELECT * FROM #TempTable


WHERE ID > @FirstRec


AND


ID < @LastRec


AND


EmployerID = @EmployerID


AND


Job_no = @Job_no





ORDER BY @WHICHCOLUMN asc





You can see that @WHICHCOLUMN is can be Surname, Age ETC, I have tried to make it a variable but, it started complaining of @FIRSTREC not defined, what's going on pls help, However, how do you combine dynamic queries with parameters as the say





"Sql server does not accept variables as part of sql"





my yahoo is abujajob@yahoo.com















































WORKING CODE without WHICHCOLUMN





CREATE PROCEDURE [GetApplicants]


@CurrentPage int,


@PageSize int,


@TotalRecords int output,


@EmployerID int,


@Job_no int,


@WhichColumn varchar,


@SortBy varchar


AS


--Create a temp table to hold the current page of data


--Add and ID column to count the records


CREATE TABLE #TempTable


(


ID int IDENTITY PRIMARY KEY,


Job_no int,


EmployerID int,


JobseekersID int,


Email varchar (100)





)


--Fill the temp table with the Customers data


INSERT INTO #TempTable


(


Job_no, EmployerID,JobseekersID,Email





)





SELECT Job_no, EmployerID,JobseekersID,Email FROM ApplicantsManagement





--Create variable to identify the first and last record that should be selected





DECLARE @myStatement varchar(500)


DECLARE @FirstRec int, @LastRec int


SELECT @FirstRec = (@CurrentPage - 1) * @PageSize


SELECT @LastRec = (@CurrentPage * @PageSize + 1)


--Select one page of data based on the record numbers above





SELECT * FROM #TempTable





WHERE ID > @FirstRec





AND





ID < @LastRec





AND





EmployerID = @EmployerID





AND Job_no = @Job_no





ORDER BY surname asc





--Return the total number of records available as an output parameter


SELECT @TotalRecords = COUNT(*) FROM Customers


GO