ROW_NUMBER() Order By dynamic string

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)

ROW_NUMBER() Order By dynamic string

Author

Topic

sduffy77
Starting Member

18 Posts

Posted - 2008-12-24 : 09:27:21

Ok, basically we need to dynamically build the order by based off params passed into a stored proc.
DECLARE @SortExp varchar(20)
DECLARE @SortDir varchar(4)

So I'm building a string with the expression and sort dir
Declare @sortString as varchar(100)
-- BEGIN Build Sort By
IF (@SortExp = 'PartnerPayment')
BEGIN
set @sortString = @SortExp + ' ' + @SortDir + ', VFT_DisplayOrder ASC'
END
ELSE
BEGIN
set @sortString = @SortExp + ' ' + @SortDir + ', PartnerPayment DESC, VFT_DisplayOrder ASC'
END
-- END Build Sort By
PRINT @sortString

it's printing it out just fine.

Now if I try to create a rank column sorted by this sortstring, it doesn't do any sort

ROW_NUMBER() OVER (ORDER BY @sortString) AS [my_RANK]

Am I missing something or is there a better way to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-24 : 10:08:23

Don't have enuf info to give you a better way, but your entire sql statement would need to be dynamic, not just your order by clause.
You can't order by parameters just like you can't
select @columName from someTable. You'd have to put the entire statement
into a paramter and then execute it
EXEC (@sqlString)

Jim

sduffy77
Starting Member

18 Posts

Posted - 2008-12-24 : 10:57:20

quote:
Originally posted by jimf

Don't have enuf info to give you a better way, but your entire sql statement would need to be dynamic, not just your order by clause.
You can't order by parameters just like you can't
select @columName from someTable. You'd have to put the entire statement
into a paramter and then execute it
EXEC (@sqlString)

Jim

Actually we're trying to get away from using dynamic sql due to the performance issues with it.

I'm playing around with doing something like this:
ROW_NUMBER() OVER (ORDER BY
CASE (@SortExp)
WHEN 'Year'
THEN
[Year]
ELSE
[PartnerPayment]
END
, [VFT_DisplayOrder] ASC) AS [my_RANK]

The problem I am running into is setting the direction.

Also read that Row_Number() is not very efficient on large result sets, can anyone comment on this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-24 : 13:17:35

Did you want to return the row_number results AND sort by them or were just using the row_number function for something to sort by?

check out this article - Various methods are discussed in the COMMENTs as well

EDIT:
I guess this would help :)
http://www.sqlteam.com/article/dynamic-order-by

Be One with the Optimizer
TG

sduffy77
Starting Member

18 Posts

Posted - 2008-12-24 : 16:30:52

quote:
Originally posted by TG

Did you want to return the row_number results AND sort by them or were just using the row_number function for something to sort by?

check out this article - Various methods are discussed in the COMMENTs as well

EDIT:
I guess this would help :)
http://www.sqlteam.com/article/dynamic-order-by

Be One with the Optimizer
TG

I need the rownumber for paging. I read this article earlier today which is what got me to thinking about using the case statement.
So far the only way I can see to use this method and allow dynamic sorts in both directions is to have two row_number fields, one ASC and one DESC... Seems like a bit of extra overhead.
Any thoughts on this method?

I'll report back with what I find.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-24 : 18:33:31

just want to confirm you read the article comments as well...

Paging is a whole other can of worms. There is a tremendous amount posted here on paging. I wouldn't do the multiple row_number() idea with large datasets.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources