P
P
Philip Petrov2016-10-30 23:01:32
MySQL
Philip Petrov, 2016-10-30 23:01:32

How to branch in stored procedures in MySQL?

Hello.
There is such a task, it is necessary to select a field from all tables that start with "form", this is done using procedures and a cursor, but in some tables there is not exactly that field and the procedure ends with an error, how to check for the existence of a field in the table and if it does not exist, then write its temporary table (err), and if it does, it will be written to the temporary table (names) and continue the execution of the procedure ? Thank you :)
PS

DELIMITER //
CREATE PROCEDURE final()
BEGIN
DECLARE tableName VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE FormsCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'form%' AND TABLE_SCHEMA = 'test' GROUP BY TABLE_NAME;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
CREATE TEMPORARY TABLE IF NOT EXISTS names(name VARCHAR(50));
CREATE TEMPORARY TABLE IF NOT EXISTS err(nameTable VARCHAR(50));
OPEN FormsCursor;
WHILE done = 0 DO
FETCH FormsCursor INTO tableName;
SET @nameTable = tableName;
SET @s = CONCAT('INSERT INTO names(name) SELECT name from ', @nameTable);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END WHILE;
CLOSE FormsCursor;
SELECT DISTINCT * FROM names;
DROP TEMPORARY TABLE names;
END //

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vitaliy Orlov, 2016-10-30
@orlov0562

1) use SHOW COLUMNS FROM .. LIKE ..
dev.mysql.com/doc/refman/5.7/en/show-columns.html
2) try LOOP
https://dev.mysql.com/doc/refman/5.6/ en/loop.html
and the usual IF ELSE inside

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question