Dyanamically sorting by columns of different types

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

SQL Server 2005 Forums

Transact-SQL (2005)

Dyanamically sorting by columns of different types

Author

Topic

davepl
Starting Member

4 Posts

Posted - 2009-08-19 : 14:52:35

I have a stored procedure for which I would like the caller to be able to specify the sort column. Pretty basic stuff. However, three of the columns are text and one is integer.

I can sort of make it work by casting the int column to text, but then of course it sorts them as text, not numeric:

	ORDER BY CASE
		WHEN @OrderBy = 'ProductName'    THEN Applications.ProductName
		WHEN @OrderBy = 'CompanyName'    THEN Applications.CompanyName
		WHEN @OrderBy = 'MainCategory'   THEN Applications.MainCategory
		WHEN @OrderBy = 'SubCategory'    THEN Applications.SubCategory
                ELSE CAST(Downloads.PopularityRank AS nvarchar(32))
	END

Since that sorts as "1", "10", "100", "101", "2", "20", it's not what I want. PopularityRank is an integer column.

So, the basic question is: how can I do this (sort dynamically by columns of differing data types) without duplicating the stored procedure?

Since it doesn't alter anything about the data returned, I'm surprised that T-SQL won't allow this, but I'm just migrating from MySQL, so Iget surprised a lot!

Davepl

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 15:37:49

Well, in this case since all the other WHENs are character based you could use:
str(PopularityRank, 16)


select pr
from   (
       select 1 as pr union all
       select 10 union all
       select 100 union all
       select 101 union all
       select 2 union all
       select 20
       ) d
order by str(pr, 16)

output:
pr
-----------
1
2
10
20
100
101

Be One with the Optimizer
TG

davepl
Starting Member

4 Posts

Posted - 2009-08-19 : 16:52:23

Yes, that'd work. But it wouldn't take advantage of the integer index for sorting. I did find that if you put each in its own CASE-END statement, it works. Like so:

ORDER BY
CASE
WHEN @OrderBy = 'ProductName' THEN Applications.ProductName
END,
CASE
WHEN @OrderBy = 'CompanyName' THEN Applications.CompanyName
END,
CASE
WHEN @OrderBy = 'MainCategory' THEN Applications.MainCategory
END,
CASE
WHEN @OrderBy = 'SubCategory' THEN Applications.SubCategory
END,
CASE
WHEN @OrderBy = 'PopularityRank' THEN Downloads.PopularityRank
END

Davepl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 02:17:54

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources