MySQL Forums
Forum List  »  Stored Procedures

Re: unable to add columns dynamically to table using alter
Posted by: Devart Team
Date: December 02, 2009 06:48AM

One more variant of this SP -

CREATE PROCEDURE testing1(v_i INT, v_O INT)
BEGIN
DECLARE v_sqlTable NATIONAL VARCHAR(4000);

DROP TEMPORARY TABLE IF EXISTS tt_tbl;
CREATE TEMPORARY TABLE tt_tbl(
CompanyID NATIONAL VARCHAR(100),
NAME NATIONAL VARCHAR(100),
Department NATIONAL VARCHAR(30)
);

WHILE v_i <= v_O
DO
SET @v_sqlTable = CONCAT('ALTER TABLE tt_tbl ADD ', '_', v_i, '_', v_O, ' INT');
PREPARE SWT_Stmt FROM @v_sqlTable;
EXECUTE SWT_Stmt;
DEALLOCATE PREPARE SWT_Stmt;
INSERT INTO tt_tbl (CompanyID, NAME, Department) VALUES (v_i, v_O, 'IT');
SET v_i = v_i + 1;
END WHILE;

SET @sql = 'SELECT * FROM tt_tbl;';
PREPARE QUERY FROM @sql;
EXECUTE QUERY;

DROP TEMPORARY TABLE IF EXISTS tt_tbl;

END

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: unable to add columns dynamically to table using alter
2335
December 02, 2009 06:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.