Need Help - Custome Paging Using ROW_NUMBER()

Hi,

I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:

<Error messages>

These are on the first SELECT Line..

Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "mort.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "FUNERAL.NUMBER" could not be bound.

</Error Messages>

<Sotred Procedure>

CREATE PROCEDURE proc_NAMEGetPaged
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER
FROM
(SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER,
ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum
FROM Name n) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

</Stored Procedure>

Any assistance in resolving this would be greatly appreciated..

Regards..

Peter.

Hi,

Have resolved my problem see code below..

<Stored Procedure>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Peter Annandale
-- Create date: 21/10/2006
-- Description: Return Subset from Names table
-- =============================================
CREATE PROCEDURE proc_NAMEGetPaged
-- Add the parameters for the stored procedure here
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT CODE, LAST_NAME, Name, TYPE, NUMBER
FROM
(SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,
ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum
FROM dbo.NAME n
LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE
LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

< /Stored Procedure>

Regards..

Peter.