my sql use a variable in the ALTER TABLE ADD COLUMN statement

Question

Hi I am looking to create a table with date as column name.

I am using this code to Add column to the table:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    ALTER TABLE dates
    ADD dateStart VARCHAR(30);

    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2017-01-01','2017-12-31');

But it is showing the error dateStart as duplicate content, because it creates a column 'dateStart' instead of date. How can I use dateStart as a varible.

When I use "INSERT INTO tablename (_date) VALUES (dateStart);" instead of ALTER TABLE statement, it doesn't show any error and it is inserting the dates to database, but as rows in '_date' column. I want the dates to be added as column name.

How can use dateStart as a variable

This is going to be a bad design of your table. – Vijunav Vastivch May 09 '17 at 01:50 — Vijunav Vastivch, May 09 '17 at 01:50

score 12 · Accepted Answer · edited Jan 29 '21 at 06:54

12

The short answer is that you cannot use variables as database, table, or column names in MySQL.

The only thing that you can do is to concatenate the sql statement as string and execute it as a prepared statement.

SET @s=CONCAT('ALTER TABLE dates ADD COLUMN `',dateStart,'` VARCHAR(30)');
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

However, adding field names dynamically in large quantities to a table usually indicates a bad design (unless your are preparing materialised pivot tables for reporting purposes out of data that cannot be changed).

edited Jan 29 '21 at 06:54

CJCombrink

3,738
1
22
38

answered May 08 '17 at 13:53

Shadow

33,525
10
51
64

just had to change @s1 in line 2 of your code to @s – Abdulla May 09 '17 at 06:05

score 2 · Answer 2 · answered May 09 '17 at 01:54

2

This going to be Add Column of your Table but this is not a good idea.

set @datestart = "2017-01-03";
set @sql = concat('Alter table tbl_1 Add Column `' ,@datestart, '`Varchar(30)');

PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

If you wanted a loop then use Cursor

answered May 09 '17 at 01:54

Vijunav Vastivch

4,153
1
16
30

1) no explanation as to why to use the above code 2) the code is the same as I proposed, so adds no additional value 3) cursors are used for looping through the results of a select. There is no need to use one here. – Shadow May 09 '17 at 05:42
Before i do this i use to test your code because i am wondering you are not using @ sign.. And the result of my test is return me an error.. that's why i generate another one. Do you want me to remove this post? – Vijunav Vastivch May 09 '17 at 05:49
I did not use a @ because datestart is a parameter to a stored proc in the question. No need to use a @ in that case. – Shadow May 09 '17 at 05:55
also your variable @s become @s1 in prepare statement. – Vijunav Vastivch May 09 '17 at 05:57
Never realized that is for stored proc.. It's my mistake. That's is why i didn't do any comment at your post. i'll just test it. Then create another one. – Vijunav Vastivch May 09 '17 at 06:06
I think this is just another option for the future user of this page. By using variable. – Vijunav Vastivch May 09 '17 at 06:11
The parameter is a variable. – Shadow May 09 '17 at 07:01
I guess your right but parameter maybe compose of variable and a variable couldn't be called as a parameter anymore. – Vijunav Vastivch May 09 '17 at 07:08
But in Declaration and using both is different. Am i right? I know your better than me. You can correct me if im wrong. – Vijunav Vastivch May 09 '17 at 07:14

score -1 · Answer 3 · edited Feb 16 '21 at 19:06

-1

I have the same issue, and this worked for me.

declare @colname varchar (128)
set @colname=convert(varchar,DATEPART(DAY,GetDate()))+'\'+convert(varchar,DATEPART(MONTH,GetDate()))+convert(varchar,DATEPART(YYYY,GetDate()))
exec ('ALTER TABLE temp ADD ['+@colname +'] int NULL')

edited Feb 16 '21 at 19:06

ush189

1,342
6
22
30

answered Feb 16 '21 at 15:28

prayora kc

9
3

1

Did you test this in MySQL? That's the database tagged in the question. I think your syntax would work in Microsoft SQL Server, but MySQL has different syntax for DECLARE, no EXEC function, and does not use square-brackets as identifier delimiters. – Bill Karwin Feb 16 '21 at 15:38

my sql use a variable in the ALTER TABLE ADD COLUMN statement

3 Answers3