Add new columns to a SQL Server table within a while loop

Question

declare @a int, @min date, @max date,@i int = 0, @columnname nvarchar(max),@b nvarchar(7) = 'aa'
select @min = min([Starting Date]) from [Quantities&Planning]
select @max = max([Finish Date]) from [Quantities&Planning]
set @a  = DATEDIFF(DAY,@min,@max)
while @i < @a
begin
set @i = @i +1
set @b = str(@i)
set @columnname = 'alter table Test1 add ' + @b + ' decimal(18,2) NULL'
PRINT @columnname
exec (@columnname)
end

My code does not work as expected. Basically I want to create new columns named as numbers like 1,2,3,4,5... Here is the error code:

Warning: Null value is eliminated by an aggregate or other SET operation. Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'alter table Test1 add decimal(18,2) NULL'.

Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'alter table Test1 add decimal(18,2) NULL'.

Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'alter table Test1 add decimal(18,2) NULL'.

Answer 1

You need parenthesis around the variable next to EXEC

...........
exec (@columnname)
...........

Alternatively you can SP_EXECUTESQL to execute the framed DDL query, which does not require parenthesis but the variable should be of NVARCHAR type

...........
EXEC SP_EXECUTESQL @columnname
...........

To debug the query you can use PRINT statement before executing

...........
PRINT @columnname
exec (@columnname)
...........