|
Subject:
|
Problems using the MAX Function
|
|
Posted By:
|
Trojan_uk
|
Post Date:
|
11/26/2003 10:21:20 AM
|
Hi,
I have used code from an article on 4Guys to page through a recordset,
what I would like to do is find the last record in the temp table so as
to show "page 6 of 3383" but whereas I would normally do something like:
select max(row_id) from tablename
This doesn't seem to work, I have pasted the stored procedure below,
thanks for any help.
CREATE PROCEDURE dbo.employee_page
(
@Page INT,
@RecsPerPage INT
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
row_id INT,
NETWORK_ID VARCHAR(3),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
LEV1_ID VARCHAR(10),
LEV2_ID VARCHAR(10),
LEV3_ID VARCHAR(10),
EMP_LAST_NAME VARCHAR(25),
EMP_FIRST_NAME VARCHAR(15),
EMP_TITLE VARCHAR(30),
CUSTOMER_ID VARCHAR(20),
LOCATION_ID VARCHAR(10),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1),
NETWORK_NAME VARCHAR(40),
LEV1_NAME VARCHAR(20),
LEV2_NAME VARCHAR(20),
LEV3_NAME VARCHAR(20),
CORP_NAME VARCHAR(40),
myID int IDENTITY
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
FROM Employee INNER JOIN
Network ON Employee.NETWORK_ID =
Network.NETWORK_ID INNER JOIN
Corporation ON Employee.NETWORK_ID =
Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
JOIN
CORPLVL1 ON Employee.NETWORK_ID =
CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
Corplvl2 ON Employee.NETWORK_ID =
Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
Employee.LEV1_ID = Corplvl2.LEV1_ID AND
Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
Corplvl3 ON Employee.NETWORK_ID =
Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
Employee.LEV1_ID = Corplvl3.LEV1_ID AND
Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID =
Corplvl3.LEV3_ID
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- 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.myID >= @LastRec
)
FROM #TempItems
WHERE myID > @FirstRec AND myID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
|
|
