SP Dynamic Order ASC/DESC - 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

SP Dynamic Order ASC/DESC

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-29 : 01:56:32

Hi Gurus,

I pretty much figured out most of the part, but I am having one last problem on dynamic sorting.

ORDER BY
CASE
WHEN @OrderBy = 1 THEN CAST(RecorDate AS VARCHAR(50))
WHEN @OrderBy = 2 THEN ApproverInitial
WHEN @OrderBy = 3 THEN RequestorInitial
-- etc...
END

The above works... but how to do Dynamic ASC and DESC?

I add the following and I understand that CASE will only return as value.

CASE
WHEN @DESC = 0 THEN ASC
WHEN @DESC = 1 THEN DESC
END

Please advise.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-29 : 02:13:13

ASC & DESC is not a value. You can't use case statement on it.

For you case, either you use dynamic SQL to do it, or use nested case statement


ORDER BY
CASE
WHEN @DESC = 0 THEN
	CASE
	WHEN @OrderBy = 1 THEN CONVERT(varchar(10), RecorDate, 112)
	WHEN @OrderBy = 2 THEN ApproverInitial
	WHEN @OrderBy = 3 THEN RequestorInitial
	-- etc...
	END 
END ASC,
CASE
WHEN @DESC = 1 THEN 
	CASE
	WHEN @OrderBy = 1 THEN CONVERT(varchar(10), RecorDate, 112)
	WHEN @OrderBy = 2 THEN ApproverInitial
	WHEN @OrderBy = 3 THEN RequestorInitial
	-- etc...
	END 
END DESC

Also you should consider convert the date using specific style number like 112 or 121. using Convert or Cast without style will give you MMM DD YYYY date format

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-03-29 : 02:29:32

Thanks again for the tips! and your suggestion works flawless again :)

I'll read the link (pretty lengthy) when I am free to do so... now I got due date to catch...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources