The variable @total in DESC LIMIT ? isn't working. If I manually set DESC LIMIT 3 then the sp runs fine and returns rows. I also tried placing the SET total in between PREPARE.
Here is the stored procedure:
DELIMITER //
CREATE PROCEDURE getTotalNET()
BEGIN
DECLARE total INT;
SET total := (SELECT COUNT(*) as item_count FROM items WHERE hostid = '12345' and key_ LIKE '%_net%' AND STATUS = '0' );
PREPARE STMT FROM
" SELECT DISTINCT itemid, clock, VALUE, ns FROM history_uint WHERE itemid IN (SELECT itemid FROM items WHERE hostid = '12345' and key_ LIKE '%_net%' AND STATUS = '0' ) AND clock >= UNIX_TIMESTAMP(NOW() - INTERVAL 120 SECOND) ORDER BY clock DESC LIMIT ?";
EXECUTE STMT USING @total ;
END //
DELIMITER ;
