CODEWITHSUNDEEP
mysqlsqlstored-procedures
udarakr
udarakr

Reputation: 546

sql stored procedure order-by issue

I have following simple stored procedure when I try to call this procedure using mysql command-line it returns result in ascending order always.

DELIMITER //
CREATE PROCEDURE `activity_pagin_desc`(
    IN `payload_context_id` VARCHAR(50),
    IN `tenant_domain` VARCHAR(100),
    IN `order_val` VARCHAR(50),
    IN `lim_val` INT,
    IN `lim_offset` INT
)
BEGIN
  SELECT *
    FROM SOCIAL_COMMENTS
    WHERE payload_context_id=payload_context_id AND tenant_domain=tenant_domain
    ORDER BY order_val DESC
    LIMIT lim_val OFFSET lim_offset;
END
//

What I'm doing wrong here.

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

That is because order_val is a constant. So, ascending or descending doesn't matter. You can use dynamic SQL, or multiple case statements:

order by (case when order_val = 'col1' then col1 end),
         (case when order_val = 'col2' then col2 end),
         . . .

The multiple case statements are useful because the logic will work regardless of the different types of the columns that are referenced.

Upvotes: 3

Related Questions