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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy