paging through recordsets usind a stored procedure and variables

[gavray]

Hi,

This method and code works fine when I'm not passing variables to the select statement. But when u include them in my select statement it returns no results at all. When there are records there.

It's based on the code from

http://WWW.4guysfromrolla.com

paging using stored procedures,

can anyone help, many thanks

gavin

here's the code

CREATE PROCEDURE SP_INSTANCE3_SEARCH

(
--using 2 variables
@varindustry VARCHAR(50),
@varregion VARCHAR(50)
)

AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
DECLARE @Page int
DECLARE @RecsPerPage int
-- Find out where we will start our records from
DECLARE @RecCount int
SELECT @RecCount = @RecsPerPage * @Page + 1


--Create a temporary table
CREATE TABLE #TempItems
(

pID int IDENTITY,
Region varchar(50),
hours varchar(50),
Archived int,
Company varchar(50),
VID int,
CompanyID varchar(50),
Jobtitle varchar(50),
DatepostedUK varchar(50),
Dateposted smalldatetime,
ID int,
industry varchar(50)


)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Region, hours, Archived, Company,VID,CompanyID,Jobtitle,DatepostedUK,Dateposted,ID,industry)
SELECT TBL_Jobtable.Region, TBL_Jobtable.hours,TBL_Jobtable.Archived , TBL_Company2.Company, TBL_Company2.VID, TBL_Jobtable.CompanyID,TBL_Jobtable.Jobtitle, TBL_Jobtable.DatepostedUK,TBL_Jobtable.Dateposted, TBL_Jobtable.ID, TBL_Jobtable.industry FROM TBL_Jobtable
RIGHT JOIN TBL_Company2
ON TBL_Company2.VID = TBL_JobTable.CompanyID
WHERE industry=@varindustry
AND region=@varregion
AND Archived='0'


-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

--here is where I'm using the variables is this the right place
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.pID >= @LastRec
)
FROM #TempItems
WHERE pID > @FirstRec AND pID < @LastRec


--RETURN @TotalRecords
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO