Procedure on CURSOR

YUSUF ATES
YUSUF ATES used Ask the Experts™
on
I want to create a procedure which has one input parameter for table name and two output parameters for 'status message' and 'return code'.  And input table name is SCORE table.
For the SCORE table, the parent tables are STUDENT and GRADE_EVENT tables. And I need to add a column (PARANT_COLUMN) to the parent tables, the datatype is INT for the column.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
not clear what you are asking for...

Author

Commented:
I want to add PARENT_COLUMN to the parent tables by creating a cursor in a procedure.
HainKurtSr. System Analyst

Commented:
I want to add PARENT_COLUMN to the parent tables by creating a cursor in a procedure.

still does not make sense to me...

what will this procedure will do, what is its purpose?
will it return something or do something, or both?
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
It will just add the same column to two tables.
HainKurtSr. System Analyst

Commented:
I keep asking, but still does not make too much sense...

so I will ask clearly:

  1. what are the tables involved
  2. what are their structures (PK, column names, data types)
  3. what will sp will do
  4. what are you passing to this sp, ie what are parameters

give sample data
  • before and after sp call

Author

Commented:
Does this help?
mysqlschema.sql
HainKurtSr. System Analyst

Commented:
it answers 1&2
still missing 3&4  + bonus question above :)

Author

Commented:
DELIMITER $

DROP PROCEDURE IF EXISTS ALTER_TABLE_PROCEDURE2$

CREATE PROCEDURE ALTER_TABLE_PROCEDURE2()
BEGIN
DECLARE V_TABLE_NAME    VARCHAR(30);
DECLARE IAM_DONE        INT DEFAULT 0;
DECLARE V_SQL_STR       VARCHAR(500);
DECLARE TBL_CUR CURSOR FOR
      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'STUDENTDB'
    ;
   
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET IAM_DONE = 1;
            OPEN TBL_CUR;
                        tbl_loop:LOOP
                              FETCH TBL_CUR INTO V_TABLE_NAME;
                    IF IAM_DONE = 1 THEN
                                    LEAVE tbl_loop;
                              END IF;
                   
                    SELECT V_TABLE_NAME;
                   
                    SET @V_SQL_STR:=
                                    CONCAT('ALTER TABLE '
                                              ,V_TABLE_NAME
                                ,' ADD HISTORY_DATE DATE'
                                )
                                ;
                              SELECT @V_SQL_STR;
                   
                    PREPARE STMT FROM @V_SQL_STR;
                    EXECUTE STMT;
                   
                        END LOOP tbl_loop;
            CLOSE TBL_CUR;
      END;
END$
DELIMITER ;



For example, this sp adds a column (HISTORY_DATE) to exist tables but I want to add a column (PARENT_COLUMN) to two tables (STUDENT, GRADE_EVENT).
:)
HainKurtSr. System Analyst

Commented:
my first shot :)

you want toa dd columns to these tables:

ALTER TABLE student ADD PARENT_COLUMN int;
ALTER TABLE grade_event ADD PARENT_COLUMN int;

Open in new window


after all these conversation, still I dont get what are you trying to do lol...

Author

Commented:
That was the way I used when I saw the question :)
But we need to do it in the cursor
HainKurtSr. System Analyst

Commented:
why do you need so much code/loop?

this should do it, call it twice for each column...

CREATE PROCEDURE 
  alter_table_procedure(in p_tableName, in p_colName, in p_colType, in p_NullType) 
begin 
  SET @V_SQL_STR:= concat('ALTER TABLE ', v_table_name, ' ADD ', p_colName, ' ', p_colType, ' ', p_NullType); 

  prepare stmt FROM @V_SQL_STR; 
  execute stmt; 
end;

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
This doesn't seem SQL Server code to me.
HainKurtSr. System Analyst

Commented:
@Vitor

you need (_):?

this is MySQL code, I hope...

Author

Commented:
DELIMITER $

DROP PROCEDURE IF EXISTS ALTER_TABLE_PROCEDURE3$

CREATE PROCEDURE ALTER_TABLE_PROCEDURE3()
BEGIN
DECLARE V_TABLE_NAME    VARCHAR(20);
DECLARE IAM_DONE        INT DEFAULT 0;
DECLARE V_SQL_STR       VARCHAR(500);
DECLARE TBL_CUR CURSOR FOR
      select table_name from information_schema.KEY_COLUMN_USAGE      
    where TABLE_SCHEMA = 'studentdb'
    and referenced_table_name = 'student'
    ;
   
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET IAM_DONE = 1;
            OPEN TBL_CUR;
                        tbl_loop:LOOP
                              FETCH TBL_CUR INTO V_TABLE_NAME;
                    IF IAM_DONE = 1 THEN
                                    LEAVE tbl_loop;
                              END IF;
                   
                    SELECT V_TABLE_NAME;
                   
                    SET @V_SQL_STR:=
                                    CONCAT('ALTER TABLE '
                                              ,V_TABLE_NAME
                                ,' ADD PARENT_COLUMN INT'
                                )
                                ;
                              SELECT @V_SQL_STR;
                   
                    PREPARE STMT FROM @V_SQL_STR;
                    EXECUTE STMT;
                   
                        END LOOP tbl_loop;
            CLOSE TBL_CUR;
      END;
END$
DELIMITER ;



what this code does is that it adds the column to the SCORE table.  Because STUDENT_ID is a PK in STUDENT table and FK in SCORE table.
What I want is to add that column to the STUDENT and GRADE_EVENT tables. I need this much code/loop because it is a part of assignment :)
(EVENT_ID is PK in GRADE_EVENT table and FK in SCORE table.  STUDENT_ID is a PK in STUDENT table and FK in SCORE table).
I need to call the parent tables by changing the code above.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
SQL Server topic removed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial