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
Last activity: Jan 15, 2025, 05:00 PM