September 9, 2008 at 1:48 am
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:-
SELECTDISTINCT 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
WHEREp.fk_projectid=@projectId
ANDlg.langid=9
AND p.parentId = 0
GROUP BY p.pageid
Thanks a lot
September 9, 2008 at 4:14 am
I dont know what exaclty u want but if u want to incorporate
ur SELECT command into Procedure then
see this................
DECLARE @LSTR NVARCHAR(300)
DECLARE @projectId INT
--SET @projectId = 90
SET @LSTR =
'SELECT ' +
'DISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum, '+
'dbo.GetTranslation( ' + CAST(@projectId AS VARCHAR(50)) + ' , 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 = ' + CAST(@projectId AS VARCHAR(50)) +
'AND lg.langid = 9' +
'AND p.parentId = 0' +
'GROUP BY p.pageid'
----------------------------------------------------
i hope this wont help you ;);)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 9, 2008 at 4:25 am
Hi Journeyman
I just want to retreive for example from record 20 to record 30. I was using this kind of stored proc before
WITH PagesRN AS
(
SELECTDISTINCT p.pageId, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum,
)
And then
SELECT *
FROM PagesRN
WHERE (@PageSize = 0)
OR
( RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize)
However I do not wish to retrieve the whole recordset when i will only display 10 or 20 records, depending on the pagesize.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply