ORDER BY - 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

ORDER BY

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-20 : 21:40:42

Hi all...
Have a problem in sorting using a variable.

DECLARE @sortItem varchar(max)
SET @sortItem = 'empno'

SELECT * from hrEmployees ORDER BY @sortItem ASC

Is it not possible? How could I sort using a variable?

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 22:23:50

[code]SELECT *
FROM hrEmployees
ORDER BY CASE @sortItem WHEN 'empno' THEN empno
WHEN 'empname' THEN empname
END[/code]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-20 : 22:34:12

@sortItem is passed dynamically. is CSVTable can be applied to this scenario?

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 22:36:45

yes. @sortItem is dynamic. But it will appear as a constant to the SELECT statement's ORDER BY clause.

CSVTable is for parsing csv string to rows. It is not suitable for such situation.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-20 : 22:57:56

what if i want

DECLARE @sortItems varchar(max)
SET @sortItems = 'empno,fullname,Age,Address'

@ORDER BY @sortItems ASC

I think it's not proper to declare like as this
ORDER BY CASE @sortItem WHEN 'empno' THEN empno
WHEN 'fullname' THEN fullname
WHEN 'Age' THEN empno
WHEN 'empno,fullname,Age,Address' THEN empno,fullname,Age,Address
END

Do i have a point?

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 23:00:05

you have to use multiple case when statement for the last one 'empno,fullname...'

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-20 : 23:20:15

KH i think i need a function to handle this situation like what CSVTable does. by the way function returns data not as string?

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 23:27:33

see
http://www.sqlteam.com/article/dynamic-order-by
http://weblogs.sqlteam.com/jeffs/archive/2007/06/05/60224.aspx

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-20 : 23:45:33

KH can you guide me with this function.

this is my function....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter function [dbo].[CSVSort](@Str varchar(7000))
returns @t table (stringval varchar(100))
as
begin

declare @c varchar(100);

set @Str = @Str
set @c = '';

SET @c = @Str
return
end

this is my select statement...
DECLARE @sortItems varchar(max)
SET @sortItems = 'empno,fullname'
select R.stringval from CSVSort(@sortItems) R

result : no data

What is wrong with my function?
In my function I just want to pass @sortItems to the function to eliminate the ''. then the function returns the data withoout a quote. So if @sortItems = 'empno,fullname'...the function returns a columnname of empno,fullname.

This is just my idea. I hope i made a sense.

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 23:55:16

This will not work. Whatever it is, the function is returning a string. And the string will be treated as constant value in the ORDER BY.

Have you read those links that i posted ?

Are you using SQL 2000 or 2005 ? If it is 2005, you can use the method in the 2nd link.

Actually there is a 3rd way. Known to some as the dark side. First explore the method in the earlier 2 links that i posted, and lastly here http://www.sommarskog.se/dynamic_sql.html. Caution "The Force is strong with this one"

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-21 : 00:03:17

KH i can't open the those links :(. I have no access to other networks... if it is ok for you, i am asking the method to paste here.

-Thanks.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-21 : 00:09:17

Only the first link but cant open the second link.

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 00:09:30

http://www.sqlteam.com/article/dynamic-order-by
http://weblogs.sqlteam.com/jeffs/archive/2007/06/05/60224.aspx

No access ? What do you mean ? these 2 links are of the same site. The 2nd one actually direct you to http://john-sheehan.com/blog/index.php/slightly-more-dynamic-order-by-in-sql-server-2005/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 00:30:23

quote:
if it is ok for you, i am asking the method to paste here

It is a long article. Best to read it over there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources