MySQL – Pass input parameter into Cursor query

cursorsMySQLstored-procedures

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 //

Best Answer

Thanks to Akina's comment using CONCAT in the where clause worked.

WHERE key_ LIKE CONCAT('sometext_', _TAG, '_sometext_', _ITEM_NAME)
Related Question