Alter table add columns from variable

Database

Alter table add columns from variable

Is it possible to create new columns in a table using variable names for the columns?

My intial guess was something along these lines:

REPLACE PROCEDURE EDW_PR_WORK_DB.PAUL_TEST ()

BEGIN

DECLARE i BYTEINT;

DECLARE c BYTEINT;

DECLARE strStat VARCHAR(9);

DECLARE strOH VARCHAR(9);

--code for creating EDW_PR_WORK_DB.PAUL_T_DATES

SELECT COUNT(*) INTO c FROM EDW_PR_WORK_DB.PAUL_T_DATES;

--code for creating EDW_PR_WORK_DB.PAUL_DATA

SET i = 1;

loopprds:

LOOP

IF i > c - 12 THEN LEAVE loopprds; END If;

SET strStat = 'STATUS_' || i;

SET strOH = 'OH_AMT_' || i;

ALTER TABLE EDW_PR_WORK_DB.PAUL_DATA ADD :strStat VARCHAR(1);

ALTER TABLE EDW_PR_WORK_DB.PAUL_DATA ADD :strOH FLOAT;

SET i = i + 1;

END LOOP loopprds;

END;

In this specific example, I have some number of months of existing data (c) and want to create two columns in EDW_PR_WORK_DB.PAUL_DATA for each month (the math for i and c make sense within the SP). This method made complete sense when writing it in Access using strings and iteration to make a table of sufficient width, but I don't know Teradata SQL well enough to know what works and what doesn't.

Thanks,

Paul
2 REPLIES
WAQ
Enthusiast

Re: Alter table add columns from variable

Trying using dynamic SQL

Enthusiast

Re: Alter table add columns from variable

Dynamic SQL is the way to go but you need to consider that the dynamic SQL statements are not validated during the stored procedure compilation, they are only validated during the stored procedure execution!