Dynamic Order BY - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Order BY

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-17 : 01:08:38

Hi,

Incorrect syntax near the keyword 'ASC'.

Where to put the ASC and DESC.

DECLARE @OrderBY CHAR(4)
BEGIN
SET @OrderBY = 'DESC'

SELECT ROW_NUMBER() OVER(PARTITION BY Items.Item ORDER BY
CASE
WHEN @OrderBY = 'ASC' THEN Items.Item ASC
WHEN @OrderBY = 'DESC' THEN Items.Item DESC
END) AS RowNumber,
Items.DateAdded ,
Items.Item ,
Items.Tags
FROM dbo.Items
END

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 01:23:10

you cant change the ordering using case statement like this. i think you can do a work around like this

DECLARE @OrderBY CHAR(4)
BEGIN
SET @OrderBY = 'DESC'

SELECT ROW_NUMBER() OVER(PARTITION BY Items.Item ORDER BY
CASE
WHEN @OrderBY = 'ASC' THEN Items.Item 
ELSE 1
END ASC,
CASE
WHEN @OrderBY = 'DESC' THEN Items.Item DESC
ELSE 1
END) AS RowNumber,
Items.DateAdded ,
Items.Item ,
Items.Tags
FROM dbo.Items
END

I assume Item is of type integer. if not set instead of 1 just string default value like ''

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 01:49:02

or

DECLARE @OrderBY CHAR(4)
BEGIN
SET @OrderBY = 'DESC'

EXEC('SELECT ROW_NUMBER() OVER(PARTITION BY Items.Item ORDER BY
Items.Item '+@OrderBY +') AS RowNumber,
Items.DateAdded ,
Items.Item ,
Items.Tags
FROM dbo.Items
END')

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-17 : 02:38:47

Hi,

Thanks for both of you.

I tried like this and it works.

DECLARE @OrderBY CHAR(4)
BEGIN
SET @OrderBY = 'DESC'
SELECT ROW_NUMBER() OVER(PARTITION BY Items.Item ORDER BY
CASE
WHEN @OrderBY = 'ASC' THEN Items.Item
END ASC,
CASE
WHEN @OrderBY = 'DESC' THEN Items.Item
END DESC) AS RowNumber,

Items.DateAdded ,
Items.Item ,
Items.Tags
FROM dbo.Items
ORDER BY
CASE
WHEN @OrderBY = 'ASC' THEN Items.Item
END ASC,
CASE
WHEN @OrderBY = 'DESC' THEN Items.Item
END DESC
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:45:16

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources