June 12, 2014 at 2:10 am
Hi
This my stored procedure
ALTER PROCEDURE [dbo].[PageWise]
@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT,
@CatId int
AS
BEGIN
SET NOCOUNT ON;
WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId) RowNumber,
n.NewId,
n.NewDate,
n.NewTitle,
nrc.NrcCategoryIdFk
FROM dbo.Category c
JOIN NewsRelCategory nrc ON c.CatId = nrc.NrcCategoryIdFk
JOIN dbo.News n ON nrc.NrcNewsIdFk = n.NewId
Where NrcCategoryIdFk=@CatId
)
SELECT NewId,
NewTitle,
NewDate,
NrcCategoryIdFk,
RowNumber
INTO #Results
FROM CTE
WHERE RowNumber = 1
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
I'm trying to paging data but page size doesn't work and it returns all data.
Help
June 12, 2014 at 2:51 am
You're partitioning your results by NewTitle. If the size of those partitions is smaller than your page size, then you will get all rows returned.
John
June 12, 2014 at 8:50 am
What I can do to fix?
June 12, 2014 at 8:52 am
If all you're trying to do is return the whole result set in pages of @PageSize rows, just remove the PARTITION BY clause from your ROW_NUMBER function.
John
June 12, 2014 at 9:10 am
I need NewTitle can be unique.
If I remove PARTITION BY it returns all NewTitle which isn't uinique.
June 12, 2014 at 9:31 am
Then you'll need to explain your requirement better, please. Perhaps you could post some sample data and expected results based on that data?
Thanks
John
June 12, 2014 at 9:43 am
Hrhb.mail (6/12/2014)
I need NewTitle can be unique.If I remove PARTITION BY it returns all NewTitle which isn't uinique.
The problem is that in your temp table RowNumber = 1 for every row. You need to add another ROW_NUMBER to your final output.
ALTER PROCEDURE [dbo].[PageWise]
@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT,
@CatId int
AS
BEGIN
SET NOCOUNT ON;
WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY n.NewTitle ORDER BY n.NewId) RowNumber,
n.NewId,
n.NewDate,
n.NewTitle,
nrc.NrcCategoryIdFk
FROM dbo.Category c
JOIN NewsRelCategory nrc ON c.CatId = nrc.NrcCategoryIdFk
JOIN dbo.News n ON nrc.NrcNewsIdFk = n.NewId
Where NrcCategoryIdFk=@CatId
)
SELECT NewId,
NewTitle,
NewDate,
NrcCategoryIdFk,
ROW_NUMBER() over (ORDER BY NewID) as RowNumber
INTO #Results
FROM CTE
WHERE RowNumber = 1
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
--EDIT--
Got bitten by the quote bug.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2014 at 9:48 am
Thanks Sean Lange 🙂
June 16, 2014 at 7:12 am
Hrhb.mail (6/14/2014)
Thanks Sean Lange 🙂
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply