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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-22 : 21:12:05
|
ali writes "SQLSERVER 7.0, NT ServicePack6
The technique that is mentioned in Garth's article "Dynamic Order By" is something that I've been wrestling with for a bit now.
My Query:
SELECT *
FROM vw_brPublishedRecomms
WHERE DateCreated >= @DateFrom
Status IN (2,CASE @CurrentOnly WHEN 1 THEN -1 WHEN 0 THEN 3 END) AND
ActionID = ISNULL(@ActionID, ActionID)AND
BrokerID = ISNULL(@BrokerID, BrokerID) AND
SIGN(pos) = ISNULL(@Direction, SIGN(pos)) AND
Code = ISNULL(@Code, Code)
ORDER BY CASE LOWER(RTRIM(@Ord))WHEN 'code' THEN code
WHEN 'sign(pos)' THEN SIGN(pos)
WHEN 'broker' THEN brokername
WHEN 'action' THEN ActionText
WHEN 'datecreated' THEN CAST(Datecreated AS CHAR(11))
ELSE CAST(Datecreated AS CHAR(11)) END DESC
My Problem: it doesn't work!
When I run this, depending on the column I specify, I get an error. If @Ord = 'broker' then i want to specify "BrokerName" as the column to order by. instead I get the following:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'ABC' to a column of data type int.
So I think, it's taking the value of the brokername col and trying to interpret it as an ordinal position.
So I specify a column number, in my case this was 16.
When I did this the columns do not seem to be ordered by any columns and I deduce from this that SQLSRV is taking the number 16 for each row and ordering by this hence no ordering!!
On further investigation I have found that:
SELECT *
FROM ATable
ORDER BY CASE WHEN 1 = 1 THEN 2 END
is different from
SELECT *
FROM ATable
ORDER BY 2
Am I being stupid somewhere (I'm scared to ask)" |
|
|
|
|
|
|
|
Dynamic order by