-1

I'm creating procedure called min_max to find minimum and maximum for every column in a table.

But I'm encountering an error for declaring the variable. See below:

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 @test='';

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;     

     SELECT CONCAT(MIN(col_name),"->",MAX(col_name)) as Min_Max_val FROM customer;

    SET @i = @i + 1;  
END LOOP the_loop;
END
//
DELIMITER ;

call min_max();

How should I define the variable correctly? What did I do wrong? I'm lost...

UPDATE: The result now:

<code>last_update</code> is one of the column names.

last_update is one of the column names in customer.

  • How should I define the variable correctly? Local variables do not need in declaration. I'm encountering an error for declaring the variable. Where is error message? I don't see it... Why do you think that the problem is in variable declaration? I think the error is NOT FOUND (i.e. cursor is empty). Declare continue handler for NOT FOUND instead of @i. – Akina Oct 16 at 12:50
  • @Akina I can't even compile this statement. I declared col_name but I'm still getting syntax error at the position I commented at. I wonder why? – user6308605 Oct 16 at 12:51
  • Ok so when I hover at the error line it says : @col_name is not valid at this position, expecting an identifier – user6308605 Oct 16 at 12:56
  • Oops... the variable for fetching into must be procedure-scoped, not user-defined local. Add DECLARE col_name VARCHAR(64); immediately after BEGIN, before cursor declaration, and use it instead of @col_name. – Akina Oct 16 at 12:58
  • It was interesting for me never. I simply remember (and sometimes forget, obtain error and remember again). – Akina Oct 16 at 13:20
0

Get rid of @num_rows and @i; you can loop without counting. There are lots of examples floating around.

You must construct the query something like

SET @stmt := CONCAT('SELECT "', col_name, '", ' MIN(', col_name, '), ',
                                              ' MAX(', col_name, ')',
                       ' FROM ', tbl_name');

The intent is to generate this (which you cannot generate in any simpler way):

SELECT "foo", MIN(foo), MAX(foo) FROM tbl

and then prepare and execute it:

PREPARE stmt FROM @stmt;
EXPECUTE stmt;
DEALLOCATE PREPARE stmt;

That will, however, give you one line per column. There won't be any hassles with datatypes.

Another approach would be to try to build (and execute):

SELECT "MINs:", MIN(col1) AS "col1", MIN(col2) AS "col2", ... FROM tbl
UNION ALL
SELECT "MAXs:", MAX(col1) AS "col1", MAX(col2) AS "col2", ... FROM tbl

which will string out the values across labeled columns.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.