Bug fix in sorting/ordering - SQL Server Forums

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.

tech_1
Posting Yak Master

129 Posts

Posted - 2014-09-21 : 08:15:50

excellent. thanks!

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?

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

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!

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources