Thursday, 27 January 2022

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';
 
 
---

1 comment:

Taylor said...

I just have to introduce this hacker that I have been working with him on getting my credit score been boosted across the Equifax, TransUnion and Experian report. He made a lot of good changes on my credit report by erasing all the past eviction, bad collections and DUI off my credit report history and also increased my FICO score above 876 across my three credit bureaus report you can contact him for all kind of hacks . Email him here support@wavedrive.tech go on their website wavedrive.tech for more details,Whatsapp No:+14106350697 if you want to chat them up,One thing i can assure you would not regret this at all he is 100% legit