ASC or DESC as a parameter - 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

SQL Server 2000 Forums

SQL Server Development (2000)

ASC or DESC as a parameter

Author

Topic

allal
Starting Member

5 Posts

Posted - 2006-04-24 : 16:23:54

Hi,
I want to pass a direction criteria (like ASC or DESC) for a SORT in a stored procedure.
EX:
CREATE PROCEDURE myProc
@prmDirection varchar(4)
AS
SELECT DISTINCT xxx, yyy FROM myTable
ORDER BY xxx @prmDirection
GO

But it does not work.
Can you kindly give me a sample?

Tx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 16:31:52

Dynamic ORDER BY article:
http://www.sqlteam.com/item.asp?ItemID=2209

Make sure to check out the comments of the article as it shows examples of how to get around the different data types.

Tara Kizer
aka tduggan

allal
Starting Member

5 Posts

Posted - 2006-04-24 : 17:06:48

Thanks for the link.
But in the sample the CASE is for the fields and not for the direction (ASC or DESC).
In fact, my real code is as follow, assuming that
@prmDirection is 'ASC' or DESC' and
@prm1 is a field condition and
@prmCriteria is the criteria field

SELECT DISTINCT table1.aaaa,table1.bbbb,table1.cccc,
table2.xxxx,table2.yyyy,
table3.zzzz
FROM table1
FROM table1
INNER JOIN
table2 ON table1.aaaa = table2.aaaa
INNER JOIN
table3 ON table2.xxxx = table3.xxxx
WHERE
CASE
WHEN @prm1 = 'abc' THEN table3.zzzz
WHEN @prm1 = 'def' THEN table1.cccc
WHEN @prm1 = 'ghi' THEN table2.yyyy
END
LIKE '%' + @prmCriteria + '%'
ORDER BY
CASE
WHEN @prmDirection = ' ASC' THEN table1.bbbb @prmDirection
WHEN @prmDirection = 'DESC' THEN table1.bbbb @prmDirection
END
GO

(This gives me an error

)
Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 17:12:32

Please check the comments of the article as it shows you how to do this.

Tara Kizer
aka tduggan

allal
Starting Member

5 Posts

Posted - 2006-04-24 : 17:21:26

Yes I read the comments.But my concern is not the field I want to sort but the direction.
Tx.

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 17:25:01

I expect it needs to be something like:


ORDER BY
CASE WHEN @prmDirection = ' ASC' THEN table1.bbbb ELSE NULL END ASC,
CASE WHEN @prmDirection = 'DESC' THEN table1.bbbb ELSE NULL END DESC

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-24 : 17:31:55

Kristen is correct. You can find lots of examples of the dynamic direction in the comments. You'll need to have a separate CASE for each direction and each conflicting data type. We have some stored procedures that use this dynamic ORDER BY approach and they have over 10 case statements to deal with this issue.

Tara Kizer
aka tduggan

allal
Starting Member

5 Posts

Posted - 2006-04-24 : 20:00:16

Tx Kristen.
This just confirms what I found elsewhere.
The problem now is that I have an error (ODBC SQLState: 42S22) telling

Error 145:Invalid column name "".
Invalid column name "".
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

And the SELECT DISTINCT is very important in my query.

Tx for helping me.

allal
Starting Member

5 Posts

Posted - 2006-04-24 : 20:41:44

Thank you all for your help.
Finally I found the solution in this link:
http://www.dbtalk.net/microsoft-public-sqlserver-programming/select-distinct-order-gives-error-177949.html

Thank you again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources