Sample Header Ad - 728x90

MySQL - Pass input parameter into Cursor query

1 vote
1 answer
2391 views
Is it possible to pass input parameter into Cursor SELECT statement WHERE clause? For some reason I think it isn't working. I'm trying to pass _TAG and _ITEM_NAME into where clause. DECLARE cursor_test cursor for SELECT itemid FROM items WHERE key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" AND STATUS = '0'; Here is the the Stored procedure: DELIMITER // CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20), _TAG VARCHAR(20), _ITEM_NAME VARCHAR(50)) BEGIN declare FINISHED BOOL default false; DECLARE cursor_test cursor for SELECT itemid FROM items WHERE hostid = @_HOSTID AND key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" AND STATUS = '0'; DECLARE CONTINUE HANDLER for not found set FINISHED := true; SET @HOSTNAME = _HOSTNAME; PREPARE STMT1 FROM "SELECT hostid INTO @_HOSTID FROM hosts WHERE NAME = ?"; EXECUTE STMT1 USING @HOSTNAME; DEALLOCATE PREPARE STMT1; open cursor_test; SET @TOTAL_VALUE := 0; loop_itemid: loop fetch cursor_test into _ITEMID; SELECT _ITEMID; if FINISHED then leave loop_itemid; end if; SET @TOTAL_VALUE := @TOTAL_VALUE + (SELECT value from history_uint WHERE itemid = _ITEMID ORDER BY clock DESC LIMIT 1); end loop loop_itemid; SELECT @TOTAL_VALUE; close cursor_test; END //
Asked by R0bert2 (121 rep)
May 29, 2020, 10:56 AM
Last activity: Jan 15, 2025, 05:00 PM