Paging with T-SQL - SQL Server Forums

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)

Paging with T-SQL

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-09-09 : 03:49:47

Hi All,

I am trying to do paging with T-SQL, and I managed to do it for a small table, retreiving pages. Here is the stored procedure

CREATE proc test_GetPagesPaging
(
@p_page int,
@p_page_size int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @p_total_rows_num int
DECLARE @p_first_selecting_row_num int
DECLARE @p_first_selecting_row_id int

SELECT @p_total_rows_num = count(pageid) from pages
SELECT @p_first_selecting_row_num = (@p_page - 1) * @p_page_size + 1

IF (@p_first_selecting_row_num <= @p_total_rows_num)
BEGIN
SET ROWCOUNT @p_first_selecting_row_num
SELECT @p_first_selecting_row_id = pageid
FROM pages
order by 1

SET ROWCOUNT @p_page_size
SELECT * FROM pages
WHERE pageid >= @p_first_selecting_row_id
ORDER BY 1
END
SET NOCOUNT OFF
END

And this works fine.

However I tried to apply it to a more complex stored procedure, which has a complex FROM clause, and did not manage. Can you please tell me how I can apply the same for this SELECT statement:-

SELECT DISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
dbo.GetTranslation(@projectId, dbo.RegexResourceMatch(MAX(p.pageTitle)), 9, p.pageid) AS PageTitle, MAX(p.pageName) AS PageName,
MAX(lg.langCode) AS Language
FROM dbo.Pages AS p INNER JOIN
dbo.PageRes AS pr ON p.pageId = pr.fk_pageId
AND p.fk_projectId = pr.fk_projectId INNER JOIN
dbo. countries AS ct ON pr.fk_countryId = ct.countryId INNER JOIN
dbo.Translations AS loc ON ct.countryId = loc.fk_countryId INNER JOIN
dbo.Languages AS lg ON loc.fk_langID = lg.langId
WHERE p.fk_projectid=@projectId
AND lg.langid=9
AND p.parentId = 0
GROUP BY p.pageid

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 09:35:06

[code]SELECT top (@p_page_size)
pageid,
PageName,
Language
FROM (
SELECT top (@p_page * @p_page_size)
p.pageId,
dbo.GetTranslation(@projectId, dbo.RegexResourceMatch(MAX(p.pageTitle)), 9, p.pageid) AS PageTitle, MAX(p.pageName) AS PageName,
MAX(lg.langCode) AS Language
FROM dbo.Pages AS p
INNER JOIN dbo.PageRes AS pr ON p.pageId = pr.fk_pageId
AND p.fk_projectId = pr.fk_projectId
INNER JOIN dbo. countries AS ct ON pr.fk_countryId = ct.countryId
INNER JOIN dbo.Translations AS loc ON ct.countryId = loc.fk_countryId
INNER JOIN dbo.Languages AS lg ON loc.fk_langID = lg.langId
WHERE p.fk_projectid=@projectId
AND lg.langid=9
AND p.parentId = 0
GROUP BY p.pageid
ORDER BY p.pageid
) AS d
ORDER BY pageid DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources