Oreder By Dynamically for more columns

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 2000 Forums

SQL Server Development (2000)

Oreder By Dynamically for more columns

Author

Topic

vipinspg
Starting Member

12 Posts

Posted - 2004-06-01 : 08:01:02

Hi All

I have to use " order by " clause dynamically.
I will explain my problem.Assume that I'm having a table employee with three filed emp_id,emp_name,emp_salary.

I'm selecting some employees based on some criteria.
Sometimes I have sort based on emp_id ascending.
Sometimes I have sort based on emp_id descending.
Sometimes I have sort based on emp_name ascending.
Sometimes I have sort based on emp_name descending.
Sometimes I have sort based on emp_salary ascending.
Sometimes I have sort based on emp_salary descending.

I'm doing this through an sp and I'm having a variable which contains what kind of sorting I want to do.If I put select case,
we need 3!(=6) cases here.Really I have to deal with table containing 15 columns.So select case won't be nice there since we need 15! cases.

Please suggest one solution for this.
It will be nice If we can do it with out dynamic sql.

Thanks
Vipins

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 08:12:12

Unless you are limiting by TOP or rowcount, you need to do the sorting in the front-end. This is a display issue.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

gates_micro
Starting Member

29 Posts

Posted - 2004-06-01 : 08:22:15

In the SP you can specify the field name as a parameter and then use that field in the ORDER BY clause.

vipinspg
Starting Member

12 Posts

Posted - 2004-06-01 : 08:34:27

About solutions suggested by derrickleggett

I can work with front end.But my plan was to change it in the back end so that I don't want to disturb the front end code.

About solutions suggested by gates_micro

Unless you are not using dynamic sql how can specify the field name as a parameter and then use that field in the ORDER BY clause?

ie

declare @v_flag varchar(10)
set @v_flag = 'emp_name'

The following statement results in errors:

select * from employee
order by @v_flag

But we have to dynamic sql.

I'm searching for a solution other than dynamic sql since I have to change a lot in the existing SPs.

Thanks for you two for your suggestions

.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 08:37:18

You either have to:

1. Do it in the front-end.
2. Use dynamic SQL.
3. Use CASE.

DECLARE
@v_flag VARCHAR(10),
@v_order CHAR(1)

SELECT
@v_flag = 'emp_name',
@v_order = 'D'

ORDER BY
CASE
WHEN @v_flag = 'emp_name' AND @v_order = 'D THEN emp_name END DESC

blah,blah,blah

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vipinspg
Starting Member

12 Posts

Posted - 2004-06-01 : 08:51:53

Thanks derrickleggett!

I'm planning to do it with friend end.

.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources