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.
Go to Top of 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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -