0

I created this MySQL Stored Procedure: (editing inside DBeaver, so it automatically handles adding / changing delimiters)

CREATE PROCEDURE databasename.QueryByFilterOnOtherDb(IN dbName VARCHAR(100), IN tableName VARCHAR(100), IN fieldName VARCHAR(100))
BEGIN
    DECLARE thisdb VARCHAR(100);
    SET thisdb = (SELECT DATABASE());
    SET @dbName = dbName;
    SET @tableName = tableName;
    SET @fieldName = fieldName;

    SET @filterquery = CONCAT('SELECT * FROM `', @thisdb , '`.`', @tableName, '`', 
        ' WHERE ', @fieldName, ' IN (SELECT `', @fieldName, '` FROM `', @dbname, '`.`', @tableName, '`);'
    );
    
    PREPARE stmt FROM @filterquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 

    /* SELECT @filterquery; */
END

SQL query result (SELECT @filterquery):

SELECT * FROM `databasename`.`eav_attribute_group` WHERE attribute_set_id IN (SELECT `attribute_set_id` FROM `otherdb`.`eav_attribute_group`);

The resultset is the result of the prepared statement (a table with all the rows/columns matching attribute_set_id, in other words, multiple columns/rows).

I want to use this result either inside the stored procedure or the resultset for another stored procedure:

PREPARE stmt FROM @filterquery;

SET @resultset= EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 

// Loop through resultset
// for each row
// SELECT @col1, @col2 IN ROW

OR

CREATE PROCEDURE AnotherProcedure()
BEGIN
SET resultset= QueryByFilterOnOtherDb('','','');
// Loop through resultset
// SELECT @col1, @col2 IN ROW
END

Is this possible?

I have to compare some Magento1 & Magento2 tables, and remove Magento2 entries so that Migration Tool can correctly work - so there are a few tables that I want to do the same. TRUNCATE is not an option for my use case with Migration Tool! (it still fails)

CvRChameleon
  • 367
  • 1
  • 5
  • 29
  • 1
    MySQL have no table-type variables. SP output cannot be used as data source. You may save this resultset into the table (temporary or static) in your prepared statement then reuse this data in your code. – Akina Mar 19 '21 at 09:48
  • Ok thanks, I was hoping for something that didn't involve creating a table, but this is the option. So when I create a temporary table, I just create a SP that reads from there, then I use a `cursor`? Is it possible to show a cursor example with `multiple columns`? – CvRChameleon Mar 19 '21 at 09:51
  • In SQL you do not need a cursor for to use table's data. Except you need to iterate over the dataset. Cursors examples can be found in [MySQL 8.0 Reference Manual / ... / Cursors](https://dev.mysql.com/doc/refman/8.0/en/cursors.html) - cursor `cur1` in the example is multi-columnar one. – Akina Mar 19 '21 at 09:58

0 Answers0