0

I'm using Microsoft SQL server management studio.

I would like to add a new column to a table (altertable1), and name that column using the data from a cell (Date) of another table (stattable1).

DECLARE @Data nvarchar(20) 
SELECT @Data = Date
FROM stattable1 
WHERE Adat=1
DECLARE @sql nvarchar(1000)
SET @sql = 'ALTER TABLE altertable1 ADD ' + @Data  + ' nvarchar(20)'
EXEC (@sql)

Executing this, I get the following error and can't find out why:

"Incorrect syntax near '2021'."

The stattable1 looks like this:

Date |Adat
2021-09-08 |1

2021-09-08 is a daily generated data:

**CONVERT(date,GETDATE())**
Adriand
  • 1
  • 1
    Why would you *want* a column that has a name that is a date? Sounds like an [XY Problem](http://xyproblem.info) to me. – Thom A Sep 08 '21 at 14:11
  • 1
    The error, however, *is* telling you the problem. `ALTER TABLE altertable1 ADD 2021-06-08 nvarchar(20);` ***is*** invalid syntax. – Thom A Sep 08 '21 at 14:13
  • Changing `EXEC(@sql)` to `PRINT @sql;` is a very powerful but underused debugging technique. – Aaron Bertrand Sep 08 '21 at 15:13

2 Answers2

0

Just like Larnu said in comment, maybe this is not a main problem for you, but if you want to do this add [ ] when you want to name column starting with number.

Like this:

SET @sql = 'ALTER TABLE altertable1 ADD [' + @Data  + '] nvarchar(20)'

And of course, naming columns by date or year is not best practice.

Marko Ivkovic
  • 1,262
  • 1
  • 11
  • 14
  • 1
    *Careful*, `'[' + @Data + ']'` is **not** injection safe. – Thom A Sep 08 '21 at 14:18
  • @Larnu, with Adriand logic everything isn't safe. I suggest he change everything about this alter table, but this is the answer to his question. – Marko Ivkovic Sep 08 '21 at 14:22
  • 2
    I don't disagree about changging the design, it's why I haven't answered, however, if you *are* injecting dynamic object names you should be using `QUOTENAME`; the above falls over as soon as you get a string with the `]` character in it. – Thom A Sep 08 '21 at 14:23
  • This is a much better way of thinking and all correct. But, because he tries with date/year I think that is problem here. Generally yes. – Marko Ivkovic Sep 08 '21 at 14:26
  • Larnu's point is that you should demonstrate best practices instead of unsafe ones even in cases where you don't believe it is absolutely necessary. Readers other than this OP will learn from this code and assume just slapping manual `[` and `]` is fine. It's not. – Aaron Bertrand Sep 08 '21 at 14:59
0

The problem with your overall design is that you seem to be adding a column to the table every day. A table is not a spreadsheet and you should be storing data for each day in a row, not in a separate column. If your reports need to look that way, there are many ways to pivot the data so that you can handle that at presentation time without creating impossible-to-maintain technical debt in your database.

The problem with your current code is that 2021-06-08 is not a valid column name, both because it starts with a number, and because it contains dashes. Even if you use a more language-friendly form like YYYYMMDD (see this article to see what I mean), it still starts with a number.

The best solution to the local problem is to not name columns that way. If you must, the proper way to escape it is to use QUOTENAME() (and not just manually slap [ and ] on either side):

DECLARE @Data nvarchar(20), @sql nvarchar(max);

SELECT @Data = Date 
  FROM dbo.stattable1 
  WHERE Adat = 1;

SET @sql = N'ALTER TABLE altertable1 
  ADD ' + QUOTENAME(@Data)  + N' nvarchar(20);';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

This also demonstrates your ability to debug a statement instead of trying to decipher the error message that came from a string you can't inspect.

Some other points to consider:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490