0

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 (?)

  • You assign @A to empty string. Why you're surprized? Use SET @A:=CONCAT('Select MAX(', @col_name, ') from customer;');. – Akina Oct 17 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.