SQL Server Order By Clause

Question

Post reply

SQL Server Order By Clause

peer_mohamed2k

Mr or Mrs. 500

Points: 510
More actions
September 1, 2008 at 1:25 am

#189909

Hi,
I have table with columns like CustomerName, CustomerAddress, CreatedDate, ImplementationDate, Revenue1, Revenue2, .............................Revenue15.
Here CustomerName, CustomerAddress - varchar(2000)
CreateDate, ImplementationDate -DateTime
All Revenue1... 15 columns are Numeric(38,2)
In a procedure, I am using Order by clause with case statement for all these columns
Like
Select * from Customer_Repo
Order By Case @OrderColumn
When 'CustomerName' Then CustomerName
When 'CustomerAddress' Then CustomerAddress
When 'CreatedDate' Then CreatedDate
When 'ImplementationDate' Then ImplementationDate
When 'Revenue1' Then Revenue1
When 'Revenue15' Then Revenue15
END
But the order by clause different datatypes, I am getting some error.
Can anyone help me on this to resolve ?
Thanks,
Peer Md.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

Go with the followinf case statement...

Select * from Customer_Repo

Order By

Case When @OrderColumn='CustomerName' Then CustomerName End,

Case When @OrderColumn='CustomerAddress' Then CustomerAddress End,

Case When @OrderColumn='CreatedDate' Then CreatedDate End,

Case When @OrderColumn='ImplementationDate' Then ImplementationDate End,

Case When @OrderColumn='Revenue1' Then Revenue1 End,

Case When @OrderColumn='Revenue15' Then Revenue15 End

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Did Atif's solution work?

What is the error?

You could do an explicit convert to character(varchar) on the numeric columns.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bcronce SSCrazy Points: 2597 More actions · Answer 3

Just tossing this out there, no idea if it's related. Does SQL treat large varchars as text? If so, I wouldn't think you could 'order by' because SQL won't compare 'text' types.

P.S. That is a question. When, if at all, does SQL treat char/varchar as a text type.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

No SQL Server does not treat large varchars as text.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rajankjohn SSCertifiable Points: 5061 More actions · Answer 5

see of this can help you -

http://rajanjohn.blogspot.com/2008/04/dynamic-tsql-vs-case.html