Sort output on diffent columns

Question

Post reply

Sort output on diffent columns

Divya Agrawal

Hall of Fame

Points: 3144
More actions
February 26, 2010 at 2:11 am

#94435

I have a set of rows to be fetched from a table sorted on the column name passed in the stored procedure.
Here is the code which i used:
Declare @ordercol varchar(100),@sql as varchar(500)
set @sql ='select top 5 *
from sys.objects
order by '+@ordercol
exec(@sql)
Is it possible to do this without using dynamic query?
--Divya

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use the CASE statement. Something like:

DECLARE @ordercol sysname,@sql AS VARCHAR(500)

SET @ordercol='object_id'

SELECT TOP 5 *

FROM sys.objects

ORDER BY

CASE @ordercol

WHEN 'object_id' THEN STR(OBJECT_ID)

WHEN 'name' THEN name

ELSE CONVERT(CHAR(10),create_date,120)

END

It's important to convert all possible CASE values to the same data type. If you'd use for instance OBJECT_ID instead of STR(OBJECT_ID) you'd get an conversion error.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 2

Thanks Lutz.

Your solution i have already used for less number of columns.

There are lots of columns around 100's in the table which i need to query. Case will be really hard to write for all 100 columns.

Is there any other good alternative?

--Divya

LutzM SSC Guru Points: 107049 More actions · Answer 3

Divya Agrawal (2/26/2010)
Thanks Lutz.
Your solution i have already used for less number of columns.
There are lots of columns around 100's in the table which i need to query. Case will be really hard to write for all 100 columns.
Is there any other good alternative?

Thank you for providing this important information. Would have helped a lot if included in the first post. :angry:

You could use dynamic SQL ,meaning: build the SELECT statement as a string and call that with sp_executesql. See BOL for details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 4

lmu92 (2/26/2010)
You could use dynamic SQL ,meaning: build the SELECT statement as a string and call that with sp_executesql. See BOL for details.

Thanks again.

I have already written in my first post that is is possible without dynamic query.

--Divya

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

What is the format of @ordercol?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

also, please give us the reasons for not wanting to use Dynamic SQL, don't make us grope around in the dark.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 7

Divya Agrawal (2/26/2010)
Is it possible to do this without using dynamic query?

It is, but it is difficult to get it right, and more importantly, to get it to stay that way.

Use dynamic SQL. It's better in the long run, and you get used to all the red text.

Coding options for dynamic search conditions: http://www.sommarskog.se/dyn-search-2005.html

The curse and blessings of dynamic SQL:http://www.sommarskog.se/dynamic_sql.html

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi