I'm creating a procedure where it will iterate over every column in a table and get maximum value of every column. This is my code:
DELIMITER // DROP PROCEDURE IF EXISTS min_max // CREATE PROCEDURE min_max() BEGIN DECLARE col_name VARCHAR(64); DECLARE col_names CURSOR FOR SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'customer' ORDER BY ordinal_position; OPEN col_names; SET @num_rows=0; SET @i=0; SET @colname = col_name; select FOUND_ROWS() into @num_rows; the_loop: LOOP IF @i > @num_rows THEN CLOSE col_names; LEAVE the_loop; END IF; FETCH col_names INTO col_name; #issue begins here, I believe SET @A:=''; SELECT CONCAT('Select MAX(', @col_name,') from customer'); Prepare stmt FROM @A; EXECUTE stmt; SET @i = @i + 1; END LOOP the_loop; END // DELIMITER ; call min_max();
However, I'm getting an error message after compiling the statement as follows:
Error Code: 1065 Query was empty
I have tried to run this statement in terminal, the error persisted.
It seems like the
SELECT statement is not even running (?)