SQL 2005 paging using RowNumber()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 paging using RowNumber()

Author

Topic

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-22 : 03:58:09

I got problem with using custom paging in sql 2005
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[searchperson_view_general]
@Search nvarchar(2000)
,@OrderBy nvarchar (2000)
,@PageSize int
,@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

--Default order by to first column
IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
SET @OrderBy = 'p.[person_id]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT distinct'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END

SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex '
SET @SQL = @SQL + ', p.[person_id]'
SET @SQL = @SQL + ', p.[userType_id]'
SET @SQL = @SQL + ', p.[fullName]'
SET @SQL = @SQL + ', p.[gender_nm]'
SET @SQL = @SQL + ', p.[dateOfBirth] '
SET @SQL = @SQL + ', p.[positionTitle]'
SET @SQL = @SQL + ' FROM dbo.[person_view] p '

IF LEN(@Search) > 0
BEGIN
SET @SQL = @SQL + @Search
END
SET @SQL = @SQL + ' ) SELECT distinct'
SET @SQL = @SQL + ' p.person_id'
SET @SQL = @SQL + ', p.userType_id'
SET @SQL = @SQL + ', p.fullName'
SET @SQL = @SQL + ', p.gender_nm'
SET @SQL = @SQL + ', (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth] '
SET @SQL = @SQL + ', p.positionTitle'
SET @SQL = @SQL + ' FROM PageIndex p '
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)

IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
exec sp_executesql @SQL

I checked my store procedure with parameters
exec [hr2b_searchperson_view_general_load]
'LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N''%ABC%'''
,' p.person_id asc ', 25 , 1

This is a actual query show :

WITH PageIndex AS
( SELECT distinct TOP 49 ROW_NUMBER() OVER
(ORDER BY p.person_id asc )
as RowIndex
, p.[person_id]
, p.[userType_id]
, p.[fullName]
, p.[gender_nm]
, p.[dateOfBirth]
, p.[positionTitle]
FROM person_view p
LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N'%ABC%' )
SELECT distinct
p.person_id
, p.userType_id
, p.fullName
, p.gender_nm
, (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth]
, p.positionTitle
FROM PageIndex p
WHERE RowIndex > 25 AND RowIndex <= 49 ORDER BY p.person_id asc

If I used this query without using DISTINCT it will return extactly number of records which I expected but It is duplicated.
Then I tried to use DISTINCT in this query number of records return is less than 25 records . Because it was duplicated records when I used LEFT OUTER JOIN.But my query will be able to use more LEFT OUTER JOIN than this query. Please help me get exactly 25 records?

This is my tables
person_view(person_id, fullname, userType_id, gender_nm, dateOfBirth, positionTitle)

Qualification(qualification_id, qualification_nm,institutionName, person_id)

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 04:07:51

You need to provide your full requirement & some sample data along with your table structures to get help on this.This will help us to analyse your data and determine how duplication is happening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 05:37:28

Where's the sample data?

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-22 : 23:38:14

My positionTitle field was duplicated so I have fixed that Thanks for your kindly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources