SPROC paging and sorting issue

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

General SQL Server Forums

New to SQL Server Programming

SPROC paging and sorting issue

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-16 : 14:07:38

I have data in a table and written a nice sproc which has an CTE and does paging wonderfully.
I now have a request to be able to order by a given column name and in a particular direction (ASC/DESC) - not so much of a problem but it seems to perform weird ordering/sorting just for that page in that it only sorts it for that result set to be returned back rather than having the current page in the correct order of sorting.

here is the SPROC:

CREATE PROCEDURE [dbo].[sp_GetProducts] (
@itemsPerPage int,
@pageNumber int,
@totalRecords int OUTPUT,
@sortByFieldName nvarchar(30) = 'ID',
@sortDir nvarchar(5) = 'ASC'
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @firstRow int
DECLARE @lastRow int

SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords = (SELECT COUNT(p.[SDSID]) FROM v_SDS_Summary p);

WITH ProductSummary AS
(
SELECT p.[ID], p.Product, p.SecondName, p.Manufacturer, p.Category,
ROW_NUMBER() OVER (ORDER BY p.ID ASC) AS RowNumber
FROM v_Product_Summary p
)

SELECT RowNumber, [ID], Product, SecondName, Manufacturer, Category
FROM ProductSummary
WHERE RowNumber BETWEEN @firstRow AND @lastRow

ORDER BY --SDSID ASC
CASE @sortByFieldName
WHEN 'ID' THEN CAST(ID as varchar(50))
WHEN 'Product' THEN Product
WHEN 'CommonName' THEN SECONDNAME
WHEN 'Manufacturer' THEN MANUFACTURER
WHEN 'Category' THEN CATEGORY
END

END

When I run it and give it a page number to go to, it brings back the results great.

when I tell it I want to sort by a column name, it seems to sort it in a weird way that I cannot even describe. basically seems to some how condense the sorting on that page only from a-z

[url]http://www.sandlerltd.co.uk/personal/SortingIssue.jpg[/url]

so here, this is sorted by Product in ASC but as you can see, the sorting is not correct. It seems to be sorted, which it technically is, but these are not the only products in the DB. There are around 3,000 products and definitely would be the page for "a" or "b" products to be shown on page number 2 as I specified.

what am I doing wrong?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-17 : 07:55:38

Order by happens after select so in your case it will only be for the page of data.

In other words of you want sorting you have to select all the data, sort it then use the offset clause to get the page.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-17 : 11:45:25

There's also good info here on how to set up various sort options:

http://technet.microsoft.com/en-us/library/dd220417.aspx#Sorting

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-20 : 04:25:42

thanks.

I managed to fix this - this happened to be that the sorting etc... was not being done within the CTE. Once I got that working... all was great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources