Dynamic ORDER BY on a computed column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Dynamic ORDER BY on a computed column?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-21 : 19:23:53

I'm having trouble creating a stored procedure that uses a dynamic order by on a computed column. Here's the scenario:


CREATE TABLE u (i int IDENTITY,name varchar(100))
go

INSERT INTO u (name) VALUES ('test1')
INSERT INTO u (name) VALUES ('tes1')
INSERT INTO u (name) VALUES ('t1')
go

create procedure p_test(@vcMatch varchar(10),@iSort int) AS
set nocount on
SELECT i,name,len(name) as length
FROM users
WHERE name like @vcMatch
ORDER BY
  CASE
   WHEN @iSort=0 THEN length 
   WHEN @iSort=1 THEN i 
  END

...the same query works fine as t-sql, but not in a stored procedure.

Any ideas?

Thanks
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:45:47

try using len(name) in the order by instead of length.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:53:25

Have come across oddities with case statements in order by's before.
looks like you can't do much to the computed column

ORDER BY length * 1
doesn't work either.

Not much fun but you could

drop procedure p_test
go
create procedure p_test
(@vcMatch varchar(10),@iSort int) AS
set nocount on
select *
from
(SELECT i,name,len(name) as length
FROM u
WHERE name like @vcMatch) as a
ORDER BY
CASE
WHEN @iSort=0 THEN length
WHEN @iSort=1 THEN i
END
go

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-21 : 19:55:37

Thanks... can't believe I didn't try that.

And now another dynamic ORDER BY question: two of my possible sorts are DESC, but one needs to be ASC. Constructing the order by this way, the ASC/DESC seems to have to go outside the CASE clause.

Here's what I need:


ORDER BY
  CASE 
    WHEN @tiSort=0 THEN columnA DESC
    WHEN @tiSort=1 THEN columnB DESC
    WHEN @tiSort=2 THEN columnC ASC
  END

...but that's not going to work.

Any ideas for that one?

Thanks!
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 20:00:47

Bad luck

ORDER BY
CASE WHEN @tiSort=0 THEN columnA else null end DESC ,
case WHEN @tiSort=1 THEN columnB else null end DESC ,
case WHEN @tiSort=2 THEN columnC else null end ASC

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-21 : 20:31:30

Thanks very much... no wonder you're a Fu Cursor.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources