MySQL Forums
Forum List  »  Stored Procedures

ORDER BY clause in stored procedure
Posted by: Ken Guiche
Date: October 09, 2022 02:38PM

Hello, I am new to MySQL and mightily struggling with a simple store procedure.

The below compiles and runs fine with the expected results in Workbench:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN SORTORDER char(5))
BEGIN
SELECT * FROM mydictionary
order by case SORTORDER
when 'HWORD' then mydictionary.HeadWord
when 'RANK' then mydictionary.Rank
end;
END

However the same thing with additional ORDER BY conditions won't even compile:
.....
order by case SORTORDER
when 'HWORD' then mydictionary.HeadWord, mydictionary.Rank
when 'RANK' then mydictionary.Rank, mydictionary.HeadWord
.....

Workbench highlights the comma after 'mydictionary.HeadWord' as the error. What is the correct syntax, or you just cannot have more than one ORDER BY condition? I have a feeling that there is a simple solution, but after hours of reading manuals and looking for examples and I still cannot tell what I am missing. I greatly appreciate if anybody could put me out of the misery.

Options: ReplyQuote


Subject
Views
Written By
Posted
ORDER BY clause in stored procedure
298
October 09, 2022 02:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.