MySQL Stored Procedures

Here is an example of SQL command which creates MySQL stored procedure named GetDiff which has 3 input and 1 output parameters:

 

 

DELIMITER $$

CREATE PROCEDURE GetDiff (
    IN  table_left VARCHAR(255),
    IN  table_right VARCHAR(255),
    IN  post_attribute VARCHAR(255),
    IN  post_type VARCHAR(255),
    OUT sql_statement VARCHAR(1000) -- used for debugging only
)
BEGIN
    SET @table_left = table_left;
    SET @table_right = table_right;
    SET @post_attribute = post_attribute;
    SET @post_type = post_type;

    SET @column_name_alias_a = CONCAT(post_attribute, '_a');
    SET @column_name_alias_b = CONCAT(post_attribute, '_b');

    SET @sql_text = CONCAT(
        'SELECT ',
            @table_left, '.ID, ',
            @table_left, '.', @post_attribute, ' AS ', @column_name_alias_a, ', ',
            @table_right, '.', @post_attribute, ' AS ', @column_name_alias_b, ' ',
            'FROM ', @table_left, ' ',
            'INNER JOIN ', @table_right, ' ON ', @table_left, '.ID = ', @table_right, '.ID ',
            'WHERE ', @table_left, '.', @post_attribute, ' != ', @table_right, '.', @post_attribute, ' ',
            'AND ', @table_left, '.post_type = \'', @post_type, '\';');
    
    SELECT @sql_text
    INTO sql_statement;
    
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DROP PREPARE stmt;
    
END$$

DELIMITER ; 

To call it and see the table which is the result of sql query built inside the procedure:

call GetDiff('posts_a', 'posts_b', 'title', 'shipment', @sql_statement);

 

To call it and see/debug the sql query built inside the procedure:

 

call GetDiff('posts_a', 'posts_b', 'title', 'shipment', @sql_statement);
select @sql_statement;

 

The result might be like:

 

SELECT posts_a.ID, posts_a.title AS title_a, posts_b.title AS title_b FROM posts_a INNER JOIN posts_b ON posts_a.ID = posts_b.ID WHERE posts_a.title != posts_b.title AND posts_a.post_type = 'shipment';

 

 

---