Solved

order by in sql

Posted on 2009-04-02
5
161 Views
Last Modified: 2012-05-06
Hi Expert,

I used
order by StreetName + ' ' + @SortOrder in store procedure,
@SortOrder  passed

then i execute store procedure
ABC 'desc'

It doesn't work.

Thanks in advance
It doesn't work



0
Comment
Question by:dshi15
5 Comments
 
LVL 11

Expert Comment

by:N R
ID: 24054340
can you show your code?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 300 total points
ID: 24054355

you can do something like this:
order by CASE when @sortOrder = 'ASC' then StreetName ASC else StreetName DESC END
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 100 total points
ID: 24054365
If you want to order based on several columns, you either need to use order by column1 asc, column2.
Alternatively, you might need to group the item in the select (column1 + ' ' + clomn2 ) as newColumn order by newColumn.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24054423
If not you will have to use dynamic SQL. so the logic you need in your sp should look like this

declare @strSQL varchar(2000)
 
set @strSQL = 'select field1, field2 from yourtable order by StreetName ' + @sortOrder
 
exec(@strSQL)

Open in new window

0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 100 total points
ID: 24054629
ORDER BY
  CASE @SortOrder WHEN 'DESC' THEN StreetName END DESC,
  StreetName ASC
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question