How to use a for loop to insert/alter multiple columns in a table?

Question

I want to insert ten columns in a table in SQL HANA Procedure.

The following is what I have done and it seems pretty okay to me but for some reason the system is throwing syntax error.

What I want: 10 new columns named Col_1, Col_2..Col_10 each of type INT.

BEGIN
--DECLARE arr INT ARRAY ;
DECLARE i INT ;
DECLARE str1 STRING;
DECLARE str2 STRING;

str1:= 'Col_';


for i in 1 ..10 do 
     str2 := :str1 || :i;
     exec 'ALTER TABLE "Table_Name" ADD (:str2 INT)';
end for;



END;

I have tried using the same alter command by typing in the column name in the ADD() instead of using a variable and it works just fine.

Answer 1

It looks like poor schema design, anyway try to concatenate it:

BEGIN
--DECLARE arr INT ARRAY ;
DECLARE i INT ;
DECLARE str1 STRING;
DECLARE str2 STRING;
DECLARE str3 STRING;

str1:= 'Col_';

for i in 1 ..10 do 
     str2 := :str1 || :i;
     str3 := 'ALTER TABLE "Table_Name" ADD ('|| :str2 ||' INT)';
     exec str3;
end for;

END;

---

If it is one time I would simply use explicit multiple alter statements:

ALTER TABLE "Table_Name" ADD (Col_1 INT);
ALTER TABLE "Table_Name" ADD (Col_2 INT);
ALTER TABLE "Table_Name" ADD (Col_3 INT);
--...
ALTER TABLE "Table_Name" ADD (Col_10 INT);