nMpjGBMg

1DELIMITER //
2
3DROP PROCEDURE IF EXISTS testproc //
4CREATE PROCEDURE testproc ( 
5                                        IN in_srclistid INT,
6                                        IN in_dstlistid INT,
7                                        OUT out_rtncode  INT
8                                )
9BEGIN
10        DECLARE sql TEXT;
11        DECLARE done INT DEFAULT 0;
12
13        SET SQL_NOTES=0;
14        SET out_rtncode=0;
15
16        SET @temptable = CONCAT('tmp_',srclistid,'_',dstlistid,'_ban');
17        -- drop temp table first if exists --
18        SET @sql = CONCAT( 'DROP TEMPORARY TABLE IF EXISTS ',@temptable );
19        PREPARE Stmt FROM @sql;
20        EXECUTE Stmt;
21        DEALLOCATE PREPARE Stmt;
22
23        -- create temp table to store banned emails from src list --
24        SET @sql = CONCAT('CREATE TEMPORARY TABLE ',@temptable,' SELECT email,list FROM testa where list=',in_srclistid);
25        PREPARE Stmt FROM @sql;
26        EXECUTE Stmt;
27        DEALLOCATE PREPARE Stmt;
28
29        DECLARE v_email VARCHAR(250);
30
31        DECLARE c1 CURSOR FOR SELECT emailaddress FROM @temptable;
32
33        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
34
35        OPEN c1;
36        REPEAT
37                FETCH c1 INTO v_email;
38                IF NOT done THEN
39                        -- START TRANSACTION; --
40                        INSERT INTO testb ( email,list,comment ) VALUES ( v_email,in_dstlistid,'test' );
41                        IF @@error_count=0 THEN
42                                SET output = CONCAT ( v_email,' from src(',in_srclistid,') is populated into dst(',in_dstlistid,')' );
43                                SET out_rtncode := out_rtncode+1;
44                                -- COMMIT; --
45
46                        ELSE
47                                SET output = CONCAT ( v_email,' from src(',srclistid,') is NOT populated into dst(',dstlistid,')' );
48                                -- ROLLBACK; --
49                        END IF
50                        SELECT output
51                END IF;
52        UNTIL done END REPEAT;
53        CLOSE c1;
54
55
56END //
57
58DELIMITER ;