Re: Stored Procedure with a "ORDER BY" parameter

[peter.hamilton-scott]

Roland, I now have this and it works ok. Maybe you mentioned this in the other thread where we discussed this concept, but does it mean you cannot use parameters in the execute statement? That is, where I had before:

execute stmt_handle using @sorder;

and @sorder was the replacement for the ? placeholder, we now know does not work. But the SP below does work. I'm wondering if you cannot have ? parameters in the prepare statement?

DELIMITER $$

DROP PROCEDURE IF EXISTS `cachelistener`.`AuditTest` $$
CREATE PROCEDURE `AuditTest`(in sorder varchar(250))
BEGIN
set @stmt_text := concat('select * from audit order by ', sorder);
prepare stmt_handle from @stmt_text;
execute stmt_handle;
deallocate prepare stmt_handle;
END $$

DELIMITER ;