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
 Bug fix in sorting/ordering

Author  Topic 

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 07:19:23
Take this:



DECLARE @pageNumber int = 1, @itemsPerPage int = 25,
@totalRecords int, @sortByFieldName nvarchar(50) = 'TrackName',

@columnName nvarchar(50) = NULL,
@columnValue nvarchar(50) = NULL


DECLARE @firstRow int
DECLARE @lastRow int

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

WITH TracksSummary AS
(
SELECT
p.TrackID, p.ArtistID, p.GenreID, p.TrackName, p.FullDuration,
ROW_NUMBER() OVER (ORDER BY CASE @sortByFieldName
WHEN 'ID' THEN CAST(TrackID as nvarchar(50))
WHEN 'TrackName' THEN TrackName
WHEN 'Duration' THEN CAST(FullDuration as nvarchar(50))
WHEN 'aid' THEN CAST(ArtistID as nvarchar(50))
END ASC) AS RowNumber
FROM Tracks p
)
SELECT
RowNumber, TrackID, ArtistID, GenreID, TrackName, FullDuration
FROM
TracksSummary
WHERE
RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY
CASE @sortByFieldName
WHEN 'ID' THEN CAST(TrackID as nvarchar(50))
WHEN 'TrackName' THEN TrackName
WHEN 'Duration' THEN CAST(FullDuration as nvarchar(50))
WHEN 'aid' THEN CAST(ArtistID as nvarchar(50))
END


Now, this works nicely in terms of ordering it by TrackName and anything "string" related.
But when I give it the ID - the ordering does not happen in the usual way an int column would be ordered by but instead by the string rules.

I understand this is because I am doing a cast from int to nvarchar but this seems to be the only solution that works when trying to do a non dynamic T-SQL.

is there a workaround for this or a way to do a correct order by when it involves a column which is of an int value?

please provide a reasonable explanation with code samples where possible.

thank you!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-21 : 07:34:16
[code] ORDER BY
CASE WHEN @sortByFieldName = 'ID' THEN TrackID END,
CASE @sortByFieldName
WHEN 'ID' THEN CAST(TrackID as nvarchar(50))
WHEN 'TrackName' THEN TrackName
WHEN 'Duration' THEN CAST(FullDuration as nvarchar(50))
WHEN 'aid' THEN CAST(ArtistID as nvarchar(50))
END[/code]If you separate out the order by columns like I have done for TrackID, you can do sort ASC or DESC independently for each column.
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 08:15:50
excellent. thanks!
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 08:21:29
ok, so lets take it a step further.
what about filtering on a column name and value supplied?

I have this in my CTE


...
FROM Tracks p
WHERE (@columnValue IS NULL AND @columnName IS NULL)
OR
CASE @columnName
WHEN 'TrackId' THEN CAST(p.TrackID as nvarchar(100))
WHEN 'TrackName' THEN p.TrackName
WHEN 'FullDuration' THEN CAST(p.FullDuration as nvarchar(100))
WHEN 'ID' THEN CAST(p.TrackID as nvarchar(100))
WHEN 'ArtistId' THEN CAST(p.ArtistID as nvarchar(50))
END
LIKE @columnValue


is there a way to do the same thing so I don't need to do the casts as well as having a proper equality operator ('=') for the int columns and a LIKE clause, as I have above, for the string cols?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-21 : 11:22:46
This type of parameter matching is usually referred to as "dynamic search conditions" or "catch-all queries". This is best done using dynamic queries (one of the very few places where dynamic queries are indeed the preferred approach). See these two articles for the rationale and examples:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search-2008.html
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 12:51:08
thanks. indeed - I wanted to avoid dynamic SQL as much as possible. eesshh!
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 12:55:36
I almost had it but got the dreaded error about unable to convert the string to an int column...


FROM Tracks p
WHERE (@columnValue IS NULL AND @columnName IS NULL)
OR
CASE @columnName
WHEN 'TrackId' THEN p.TrackID-- CAST(p.TrackID as nvarchar(100))
WHEN 'FullDuration' THEN p.FullDuration -- CAST(p.FullDuration as nvarchar(100))
WHEN 'ID' THEN p.TrackID -- CAST(p.TrackID as nvarchar(100))
WHEN 'ArtistId' THEN p.ArtistID -- CAST(p.ArtistID as nvarchar(50))
END
= @columnValue
OR
CASE @columnName
WHEN 'TrackName' THEN p.TrackName
END
LIKE @columnValue
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-22 : 08:53:18
Separate out each condition into its own like shown below:
FROM Tracks p
WHERE (@columnValue IS NULL AND @columnName IS NULL)
OR (@columnName = 'TrackId' AND p.TrackID = @columnValue)
OR (@columnName = 'FullDuration' AND p.FullDuration = @columnValue)
-- and so on
To reiterate what I was alluding to earlier - doing the catch-all-queries in this manner often leads to very poor performance because of what they call "parameter sniffing". Because of that, the method described in those two articles is preferred.
Go to Top of Page
   

- Advertisement -